Sql Comparing two tables: 2012

Thursday, March 29, 2012

Concatenate strings in group column

I have following problem:
I want to display a concatenated string in a group row of a report.
The grouping works fine, i can "sum" numerical values etc. as
expected, but i could not find a way to concatenate strings (from the
rows that make up the group). "Join(...)" does not work (gives me an
error message).
I can use "First(...) & Last(...)" to concatenate the string columns
from the first and last row of the group, but couldn't find a way to
concatenate the string columns from all rows...
I am using SQL Server 2005 / Visual Studio 2005.I use a CLR aggregate directly in SQL Server. Hopes your datasource is SQL
Server 2005.
In reports, I use Replace function to change my separator character.
--
Jean-Pierre Riehl
http://blog.djeepy1.net
http://www.bewise.fr
"PR" <pr_gg@.egal.de> wrote in message
news:76e4156a-8805-4e71-927a-7f2c848f295d@.q77g2000hsh.googlegroups.com...
>I have following problem:
> I want to display a concatenated string in a group row of a report.
> The grouping works fine, i can "sum" numerical values etc. as
> expected, but i could not find a way to concatenate strings (from the
> rows that make up the group). "Join(...)" does not work (gives me an
> error message).
> I can use "First(...) & Last(...)" to concatenate the string columns
> from the first and last row of the group, but couldn't find a way to
> concatenate the string columns from all rows...
> I am using SQL Server 2005 / Visual Studio 2005.
>
>|||On Jan 17, 8:21 am, "Jean-Pierre Riehl" <jean-pierre.ri...@.bewise.fr>
wrote:
> I use a CLR aggregate directly in SQL Server. Hopes your datasource is SQL
> Server 2005.
> In reports, I use Replace function to change my separator character.
> --
> Jean-Pierre Riehlhttp://blog.djeepy1.nethttp://www.bewise.fr
> "PR" <pr...@.egal.de> wrote in message
> news:76e4156a-8805-4e71-927a-7f2c848f295d@.q77g2000hsh.googlegroups.com...
>
thanks for the idea. I do use SQL Server 2005, however, since this is
a "group" column in a report, i can't use SQL to concatenate the
strings as i deal with a grouped subset of the sql query result...|||On Jan 16, 9:31=A0am, PR <pr...@.egal.de> wrote:
> I have following problem:
> I want to display a concatenated string in a group row of a report.
> The grouping works fine, i can "sum" numerical values etc. as
> expected, but i could not find a way to concatenate strings (from the
> rows that make up the group). "Join(...)" does not work (gives me an
> error message).
> I can use "First(...) & Last(...)" =A0to concatenate the string columns
> from the first and last row of the group, but couldn't find a way to
> concatenate the string columns from all rows...
> I am using SQL Server 2005 / Visual Studio 2005.
I'm having a hard time visualizing what you want to display. Do you
want your concantenation in each detail row or just in a group header?
Can you mock up an example?|||On Jan 21, 4:06 pm, toolman <t...@.infocision.com> wrote:
> I'm having a hard time visualizing what you want to display. Do you
> want your concantenation in each detail row or just in a group header?
> Can you mock up an example?
I want the concatenation in each detail row. I have some group "sum"s
that work fine, and want to do the same for a string. Basically it's
just a normal grouping of columns with group sums etc.. just that i
need to concatenate a string besides building the group sums.|||On Jan 22, 4:23=A0am, PR <pr...@.egal.de> wrote:
> On Jan 21, 4:06 pm, toolman <t...@.infocision.com> wrote:
> > I'm having a hard time visualizing what you want to display. =A0Do you
> > want your concantenation in each detail row or just in a group header?
> > Can you mock up an example?
> I want the concatenation in each detail row. I have some group "sum"s
> that work fine, and want to do the same for a string. Basically it's
> just a normal grouping of columns with group sums etc.. just that i
> need to concatenate a string besides building the group sums.
Is this what you want?
=3D"String Expression " & SUM(Fields!Name.Value)
It would look something like:
String Expression 1234.56
If you're wanting to concantenate actual field values, say like if
you're grouping on a combination of fields then:
=3DFields!GroupField1.Value & ", " & Fields!GroupField2.Value
would give you something like City, State or Company, Division
HTH|||On Jan 22, 8:57 pm, toolman <t...@.infocision.com> wrote:
> Is this what you want?
> ="String Expression " & SUM(Fields!Name.Value)
> It would look something like:
> String Expression 1234.56
> If you're wanting to concantenate actual field values, say like if
> you're grouping on a combination of fields then:
> =Fields!GroupField1.Value & ", " & Fields!GroupField2.Value
> would give you something like City, State or Company, Division
> HTH
Sorry, no, i don't want to concatenate strings with the sum value of
the group.
I have a grouping in the report, where a sum value (i.e. =Sum(Fields!
Name.NumValue)) is written to one textfield of the row.
What i want to do is concatenate a string from the groups result set -
pretty much like the "Sum" sums up the numerical values from the
result set over the rows. If "Join" would work it would look like
=Join(Fields!Name.StringValue) for the other text field.. but
unfortunately that does not work.
Any ideas ?|||On Jan 23, 3:50=A0am, PR <pr...@.egal.de> wrote:
> On Jan 22, 8:57 pm, toolman <t...@.infocision.com> wrote:
>
> > Is this what you want?
> > =3D"String Expression " & SUM(Fields!Name.Value)
> > It would look something like:
> > String Expression 1234.56
> > If you're wanting to concantenate actual field values, say like if
> > you're grouping on a combination of fields then:
> > =3DFields!GroupField1.Value & ", " & Fields!GroupField2.Value
> > would give you something like City, State or Company, Division
> > HTH
> Sorry, no, i don't want to concatenate strings with the sum value of
> the group.
> I have a grouping in the report, where a sum value (i.e. =3DSum(Fields!
> Name.NumValue)) is written to one textfield of the row.
> What i want to do is concatenate a string from the groups result set -
> pretty much like the "Sum" sums up the numerical values from the
> result set over the rows. If "Join" would work it would look like
> =3DJoin(Fields!Name.StringValue) for the other text field.. but
> unfortunately that does not work.
> Any ideas ?
I think if you can get your Fields!Name.StringValue values into an
array, you could then use Join() to get what you want. Unfortunately,
I'm not enough of a VB or .NET guy to guide you through that.
Hopefully someone else can jump at this point. Sorry I can't get you
further.|||On Jan 23, 5:03 pm, toolman <t...@.infocision.com> wrote:
> I think if you can get your Fields!Name.StringValue values into an
> array, you could then use Join() to get what you want. Unfortunately,
> I'm not enough of a VB or .NET guy to guide you through that.
> Hopefully someone else can jump at this point. Sorry I can't get you
> further.
Thanks for your effort. Yes, Join() requires an array.. and i have no
idea how to convert the string values into an array to do that (and
could not find anything about it in the documentation).|||Hello, PR.
Did you find any solution to your problem? I have the same issue and I can't
find any solution for this...
Thank you.
--
Alexandra
"PR" wrote:
> On Jan 23, 5:03 pm, toolman <t...@.infocision.com> wrote:
> >
> > I think if you can get your Fields!Name.StringValue values into an
> > array, you could then use Join() to get what you want. Unfortunately,
> > I'm not enough of a VB or .NET guy to guide you through that.
> > Hopefully someone else can jump at this point. Sorry I can't get you
> > further.
> Thanks for your effort. Yes, Join() requires an array.. and i have no
> idea how to convert the string values into an array to do that (and
> could not find anything about it in the documentation).
>|||On Feb 22, 12:13 pm, Alexandra Ribeiro
<AlexandraRibe...@.discussions.microsoft.com> wrote:
> Hello, PR.
> Did you find any solution to your problem? I have the same issue and I can't
> find any solution for this...
> Thank you.
> --
> Alexandra
>
Hi Alexandra,
no, i haven't found a solution for this. Maybe using a sub-report is
an option, but i haven't tried this yet.sql sql

Concatenate strings from different rows

Hello.

I have a table like:

Id Name Description OrderId
1 Microsoft This is a 0
1 Microsoft huge company. 1

I need to create a select query that will concatenate description for both entries and output it like

Microsoft - This is a huge company.

Try using the USE XML PATH () as part of the select statement; maybe something like:

Code Snippet

declare @.testData table
( id integer,
[Name] varchar(10),
Description varchar(15),
orderId integer
)
insert into @.testData
select 1, 'Microsoft', 'This is a', 0 union all
select 1, 'Microsoft', 'huge company.', 1

select distinct
[Name] + ' - ' +
( select description + ' ' as [text()]
from @.testData b
where a.id = b.id
order by orderId
for xml path ('')
) as outputString
from @.testData a

/*
outputString
--
Microsoft - This is a huge company.
*/

Concatenate strings after assigning text in place of bit strings

I have a whole bunch of bit fields in an SQL data base, which makes it a little messy to report on.

I thought a nice idea would be to assigne a text string/null value to each bit field and concatenate all of them into a result.

This is the basic logic goes soemthing like this:

select case new_accountant = 1 then 'acct/' end +
case new_advisor = 1 then 'adv/' end +
case new_attorney = 1 then 'atty/' end as String
from new_database

The output would be

Null, acct/, adv/, atty, acct/adv/, acct/atty/... acct/adv/atty/

So far, nothing I have tried has worked.

Any ideas?

I solved my own problem:

I needed to include an else statement

select fullname,
case when new_accountant = 1 then 'acct/' else '' end +
case when new_advisor = 1 then 'adv/' else '' end +
case when new_attorney = 1 then 'atty/' else '' end as Str1
from dbo.newdatabase

|||

I solved my own problem:

select fullname,
case when new_accountant = 1 then 'acct/' else '' end +
case when new_advisor = 1 then 'adv/' else '' end +
case when new_attorney = 1 then 'atty/' else '' end as Str1
from dbo.database

Concatenate String and Pass to FORMSOF?

The following query works perfectly (returning all words on a list called
"Dolch" that do not contain a form of "doing"):
SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
FROM dbo.Dolch LEFT OUTER JOIN
dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
'FORMSOF(INFLECTIONAL, "doing")')
WHERE (dbo.CombinedLexicons.vchWord IS NULL)
However, what I really want to do requires me to piece two strings together,
resulting in a word like "doing". Any time I try to concatinate strings to
get this parameter, I get an error.
For example:
SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
FROM dbo.Dolch LEFT OUTER JOIN
dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
'FORMSOF(INFLECTIONAL, "do' + 'ing")')
WHERE (dbo.CombinedLexicons.vchWord IS NULL)
I have also tried using & (as in "do' & 'ing") and various forms of single
and double quotes. Does anyone know a combination that will work?
FYI, in case this query looks goofy because of the unused "CombinedLexicons"
table, it is because the end result should be a working form of the
following...
Figuring out the string concatination is just a step toward this goal:
SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
FROM dbo.Dolch LEFT OUTER JOIN
dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
'FORMSOF(INFLECTIONAL, ' + dbo.CombinedLexicons.vchWord + ')')
WHERE (dbo.CombinedLexicons.vchWord IS NULL)
Thanks!
Have you tried using a variable
Something like
DECLARE @.String varchar(100)
SET @.String = 'do' + 'ing'
SET @.String = ' FORMSOF (INFLECTIONAL,"' + @.String + '")'
--PRINT @.String
SELECT ..........................
WHERE CONTAINS(dbo.Dolch.vchWord, @.String)
Andy
"HumanJHawkins" wrote:

> The following query works perfectly (returning all words on a list called
> "Dolch" that do not contain a form of "doing"):
> SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
> FROM dbo.Dolch LEFT OUTER JOIN
> dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
> 'FORMSOF(INFLECTIONAL, "doing")')
> WHERE (dbo.CombinedLexicons.vchWord IS NULL)
> However, what I really want to do requires me to piece two strings together,
> resulting in a word like "doing". Any time I try to concatinate strings to
> get this parameter, I get an error.
> For example:
> SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
> FROM dbo.Dolch LEFT OUTER JOIN
> dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
> 'FORMSOF(INFLECTIONAL, "do' + 'ing")')
> WHERE (dbo.CombinedLexicons.vchWord IS NULL)
> I have also tried using & (as in "do' & 'ing") and various forms of single
> and double quotes. Does anyone know a combination that will work?
> FYI, in case this query looks goofy because of the unused "CombinedLexicons"
> table, it is because the end result should be a working form of the
> following...
> Figuring out the string concatination is just a step toward this goal:
> SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
> FROM dbo.Dolch LEFT OUTER JOIN
> dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
> 'FORMSOF(INFLECTIONAL, ' + dbo.CombinedLexicons.vchWord + ')')
> WHERE (dbo.CombinedLexicons.vchWord IS NULL)
> Thanks!
>
>
>
|||Would this work for you?
declare @.string varchar(2000)
declare @.searchphrase varchar(200)
set @.searchphrase='do ing'
set @.string='SELECT [Dolch] AS [List Name], dbo.Dolch.vchWord '
select @.string=@.string+ ' FROM dbo.Dolch LEFT OUTER JOIN'
select @.string=@.string+ ' dbo.CombinedLexicons ON
CONTAINS(dbo.Dolch.vchWord,''FORMSOF(INFLECTIONAL, '
select @.string=@.string +replace(@.searchphrase,' ','')
select @.string=@.string +')'' WHERE (dbo.CombinedLexicons.vchWord IS NULL)'
print @.string
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"HumanJHawkins" <NoSpam@.NoSpam.Net> wrote in message
news:ZGmJd.5174$r27.4041@.newsread1.news.pas.earthl ink.net...
> The following query works perfectly (returning all words on a list called
> "Dolch" that do not contain a form of "doing"):
> SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
> FROM dbo.Dolch LEFT OUTER JOIN
> dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
> 'FORMSOF(INFLECTIONAL, "doing")')
> WHERE (dbo.CombinedLexicons.vchWord IS NULL)
> However, what I really want to do requires me to piece two strings
together,
> resulting in a word like "doing". Any time I try to concatinate strings to
> get this parameter, I get an error.
> For example:
> SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
> FROM dbo.Dolch LEFT OUTER JOIN
> dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
> 'FORMSOF(INFLECTIONAL, "do' + 'ing")')
> WHERE (dbo.CombinedLexicons.vchWord IS NULL)
> I have also tried using & (as in "do' & 'ing") and various forms of single
> and double quotes. Does anyone know a combination that will work?
> FYI, in case this query looks goofy because of the unused
"CombinedLexicons"
> table, it is because the end result should be a working form of the
> following...
> Figuring out the string concatination is just a step toward this goal:
> SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
> FROM dbo.Dolch LEFT OUTER JOIN
> dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
> 'FORMSOF(INFLECTIONAL, ' + dbo.CombinedLexicons.vchWord + ')')
> WHERE (dbo.CombinedLexicons.vchWord IS NULL)
> Thanks!
>
>
>
|||"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OrzwtawAFHA.1388@.TK2MSFTNGP09.phx.gbl...
> Would this work for you?
>
> declare @.string varchar(2000)
> declare @.searchphrase varchar(200)
> set @.searchphrase='do ing'
> set @.string='SELECT [Dolch] AS [List Name], dbo.Dolch.vchWord '
> select @.string=@.string+ ' FROM dbo.Dolch LEFT OUTER JOIN'
> select @.string=@.string+ ' dbo.CombinedLexicons ON
> CONTAINS(dbo.Dolch.vchWord,''FORMSOF(INFLECTIONAL, '
> select @.string=@.string +replace(@.searchphrase,' ','')
> select @.string=@.string +')'' WHERE (dbo.CombinedLexicons.vchWord IS NULL)'
> print @.string
It's taking me a while to see if this will work. Thanks for the suggestion.
It looks like a good path to take.

Concatenate String and Pass to FORMSOF?

The following query works perfectly (returning all words on a list called
"Dolch" that do not contain a form of "doing"):
SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
FROM dbo.Dolch LEFT OUTER JOIN
dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
'FORMSOF(INFLECTIONAL, "doing")')
WHERE (dbo.CombinedLexicons.vchWord IS NULL)
However, what I really want to do requires me to piece two strings together,
resulting in a word like "doing". Any time I try to concatinate strings to
get this parameter, I get an error.
For example:
SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
FROM dbo.Dolch LEFT OUTER JOIN
dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
'FORMSOF(INFLECTIONAL, "do' + 'ing")')
WHERE (dbo.CombinedLexicons.vchWord IS NULL)
I have also tried using & (as in "do' & 'ing") and various forms of single
and double quotes. Does anyone know a combination that will work?
FYI, in case this query looks goofy because of the unused "CombinedLexicons"
table, it is because the end result should be a working form of the
following...
Figuring out the string concatination is just a step toward this goal:
SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
FROM dbo.Dolch LEFT OUTER JOIN
dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
'FORMSOF(INFLECTIONAL, ' + dbo.CombinedLexicons.vchWord + ')')
WHERE (dbo.CombinedLexicons.vchWord IS NULL)
Thanks!Have you tried using a variable
Something like
DECLARE @.String varchar(100)
SET @.String = 'do' + 'ing'
SET @.String = ' FORMSOF (INFLECTIONAL,"' + @.String + '")'
--PRINT @.String
SELECT ..........................
WHERE CONTAINS(dbo.Dolch.vchWord, @.String)
Andy
"HumanJHawkins" wrote:

> The following query works perfectly (returning all words on a list called
> "Dolch" that do not contain a form of "doing"):
> SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
> FROM dbo.Dolch LEFT OUTER JOIN
> dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
> 'FORMSOF(INFLECTIONAL, "doing")')
> WHERE (dbo.CombinedLexicons.vchWord IS NULL)
> However, what I really want to do requires me to piece two strings togethe
r,
> resulting in a word like "doing". Any time I try to concatinate strings to
> get this parameter, I get an error.
> For example:
> SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
> FROM dbo.Dolch LEFT OUTER JOIN
> dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
> 'FORMSOF(INFLECTIONAL, "do' + 'ing")')
> WHERE (dbo.CombinedLexicons.vchWord IS NULL)
> I have also tried using & (as in "do' & 'ing") and various forms of single
> and double quotes. Does anyone know a combination that will work?
> FYI, in case this query looks goofy because of the unused "CombinedLexicon
s"
> table, it is because the end result should be a working form of the
> following...
> Figuring out the string concatination is just a step toward this goal:
> SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
> FROM dbo.Dolch LEFT OUTER JOIN
> dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
> 'FORMSOF(INFLECTIONAL, ' + dbo.CombinedLexicons.vchWord + ')')
> WHERE (dbo.CombinedLexicons.vchWord IS NULL)
> Thanks!
>
>
>|||Would this work for you?
declare @.string varchar(2000)
declare @.searchphrase varchar(200)
set @.searchphrase='do ing'
set @.string='SELECT [Dolch] AS [List Name], dbo.Dolch.vchWord '
select @.string=@.string+ ' FROM dbo.Dolch LEFT OUTER JOIN'
select @.string=@.string+ ' dbo.CombinedLexicons ON
CONTAINS(dbo.Dolch.vchWord,''FORMSOF(INFLECTIONAL, '
select @.string=@.string +replace(@.searchphrase,' ','')
select @.string=@.string +')'' WHERE (dbo.CombinedLexicons.vchWord IS NULL)'
print @.string
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"HumanJHawkins" <NoSpam@.NoSpam.Net> wrote in message
news:ZGmJd.5174$r27.4041@.newsread1.news.pas.earthlink.net...
> The following query works perfectly (returning all words on a list called
> "Dolch" that do not contain a form of "doing"):
> SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
> FROM dbo.Dolch LEFT OUTER JOIN
> dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
> 'FORMSOF(INFLECTIONAL, "doing")')
> WHERE (dbo.CombinedLexicons.vchWord IS NULL)
> However, what I really want to do requires me to piece two strings
together,
> resulting in a word like "doing". Any time I try to concatinate strings to
> get this parameter, I get an error.
> For example:
> SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
> FROM dbo.Dolch LEFT OUTER JOIN
> dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
> 'FORMSOF(INFLECTIONAL, "do' + 'ing")')
> WHERE (dbo.CombinedLexicons.vchWord IS NULL)
> I have also tried using & (as in "do' & 'ing") and various forms of single
> and double quotes. Does anyone know a combination that will work?
> FYI, in case this query looks goofy because of the unused
"CombinedLexicons"
> table, it is because the end result should be a working form of the
> following...
> Figuring out the string concatination is just a step toward this goal:
> SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
> FROM dbo.Dolch LEFT OUTER JOIN
> dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
> 'FORMSOF(INFLECTIONAL, ' + dbo.CombinedLexicons.vchWord + ')')
> WHERE (dbo.CombinedLexicons.vchWord IS NULL)
> Thanks!
>
>
>|||"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OrzwtawAFHA.1388@.TK2MSFTNGP09.phx.gbl...
> Would this work for you?
>
> declare @.string varchar(2000)
> declare @.searchphrase varchar(200)
> set @.searchphrase='do ing'
> set @.string='SELECT [Dolch] AS [List Name], dbo.Dolch.vchWord '
> select @.string=@.string+ ' FROM dbo.Dolch LEFT OUTER JOIN'
> select @.string=@.string+ ' dbo.CombinedLexicons ON
> CONTAINS(dbo.Dolch.vchWord,''FORMSOF(INFLECTIONAL, '
> select @.string=@.string +replace(@.searchphrase,' ','')
> select @.string=@.string +')'' WHERE (dbo.CombinedLexicons.vchWord IS NULL)'
> print @.string
It's taking me a while to see if this will work. Thanks for the suggestion.
It looks like a good path to take.sql sql

Concatenate String and Pass to FORMSOF?

The following query works perfectly (returning all words on a list called
"Dolch" that do not contain a form of "doing"):

SELECT 'Dolch' AS[List Name], dbo.Dolch.vchWord
FROM dbo.Dolch LEFT OUTER JOIN
dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
'FORMSOF(INFLECTIONAL, "doing")')
WHERE (dbo.CombinedLexicons.vchWord IS NULL)

However, what I really want to do requires me to piece two strings together,
resulting in a word like "doing". Any time I try to concatinate strings to
get this parameter, I get an error.

For example:

SELECT 'Dolch' AS[List Name], dbo.Dolch.vchWord
FROM dbo.Dolch LEFT OUTER JOIN
dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
'FORMSOF(INFLECTIONAL, "do' + 'ing")')
WHERE (dbo.CombinedLexicons.vchWord IS NULL)

I have also tried using & (as in "do' & 'ing") and various forms of single
and double quotes. Does anyone know a combination that will work?

FYI, in case this query looks goofy because of the unused "CombinedLexicons"
table, it is because the end result should be a working form of the
following...
Figuring out the string concatination is just a step toward this goal:

SELECT 'Dolch' AS[List Name], dbo.Dolch.vchWord
FROM dbo.Dolch LEFT OUTER JOIN
dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
'FORMSOF(INFLECTIONAL, ' + dbo.CombinedLexicons.vchWord + ')')
WHERE (dbo.CombinedLexicons.vchWord IS NULL)

Thanks!Would this work for you?

declare @.string varchar(2000)
declare @.searchphrase varchar(200)
set @.searchphrase='do ing'
set @.string='SELECT [Dolch] AS[List Name], dbo.Dolch.vchWord '
select @.string=@.string+ ' FROM dbo.Dolch LEFT OUTER JOIN'
select @.string=@.string+ ' dbo.CombinedLexicons ON
CONTAINS(dbo.Dolch.vchWord,''FORMSOF(INFLECTIONAL, '
select @.string=@.string +replace(@.searchphrase,' ','')
select @.string=@.string +')'' WHERE (dbo.CombinedLexicons.vchWord IS NULL)'
print @.string

--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"HumanJHawkins" <NoSpam@.NoSpam.Net> wrote in message
news:ZGmJd.5174$r27.4041@.newsread1.news.pas.earthl ink.net...
> The following query works perfectly (returning all words on a list called
> "Dolch" that do not contain a form of "doing"):
> SELECT 'Dolch' AS[List Name], dbo.Dolch.vchWord
> FROM dbo.Dolch LEFT OUTER JOIN
> dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
> 'FORMSOF(INFLECTIONAL, "doing")')
> WHERE (dbo.CombinedLexicons.vchWord IS NULL)
> However, what I really want to do requires me to piece two strings
together,
> resulting in a word like "doing". Any time I try to concatinate strings to
> get this parameter, I get an error.
> For example:
> SELECT 'Dolch' AS[List Name], dbo.Dolch.vchWord
> FROM dbo.Dolch LEFT OUTER JOIN
> dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
> 'FORMSOF(INFLECTIONAL, "do' + 'ing")')
> WHERE (dbo.CombinedLexicons.vchWord IS NULL)
> I have also tried using & (as in "do' & 'ing") and various forms of single
> and double quotes. Does anyone know a combination that will work?
> FYI, in case this query looks goofy because of the unused
"CombinedLexicons"
> table, it is because the end result should be a working form of the
> following...
> Figuring out the string concatination is just a step toward this goal:
> SELECT 'Dolch' AS[List Name], dbo.Dolch.vchWord
> FROM dbo.Dolch LEFT OUTER JOIN
> dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
> 'FORMSOF(INFLECTIONAL, ' + dbo.CombinedLexicons.vchWord + ')')
> WHERE (dbo.CombinedLexicons.vchWord IS NULL)
> Thanks!
>|||"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OrzwtawAFHA.1388@.TK2MSFTNGP09.phx.gbl...
> Would this work for you?
>
> declare @.string varchar(2000)
> declare @.searchphrase varchar(200)
> set @.searchphrase='do ing'
> set @.string='SELECT [Dolch] AS[List Name], dbo.Dolch.vchWord '
> select @.string=@.string+ ' FROM dbo.Dolch LEFT OUTER JOIN'
> select @.string=@.string+ ' dbo.CombinedLexicons ON
> CONTAINS(dbo.Dolch.vchWord,''FORMSOF(INFLECTIONAL, '
> select @.string=@.string +replace(@.searchphrase,' ','')
> select @.string=@.string +')'' WHERE (dbo.CombinedLexicons.vchWord IS NULL)'
> print @.string

It's taking me a while to see if this will work. Thanks for the suggestion.
It looks like a good path to take.

Concatenate string

Please help me with this if you can.
I have one table with CustomerID and some other data.
In other table i have CustomerID(the link with the first table) and Agent
The relation of the first with the second one is ONE TO MANY.
I want something like this:
Customer,'Agent1,Agent2,Agent3'

Is it possible.
Please help :)looks like you are trying to reverse pivot...

1) Are there always three agent rows per customer id ?
2) Is there a field which identifies the agent number per customer id?

throw up the ddl to these tables|||First Table
CustomerID|CustomerName

SecondTable
CustomerID|AgentName

Ex Records:

First Table
1| Gigi
2|Vasile

Second Table
1|Ionescu
1|Vasilescu
1|George
2|Marin
2|Preda

I want:
1|'Gigi'|'Ionescu,Vasilescu,George'
2|'Vasile'|'Marin,Preda'|||I did find a solution.
But i'm not sure that is the best one.
I use a function in MS SQL Server and a Cusor inside.
Here it is the function.
Anyway, i know that Cursors are slowly so please give me another solution if you know.

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER FUNCTION admin_GetAgentsNames (@.CustomerId int)
RETURNS varchar(1000)
AS

BEGIN
DECLARE @.AgentsNames VARCHAR(1000)
DECLARE @.AgentFName VARCHAR(50)
DECLARE @.AgentLName VARCHAR(50)
DECLARE @.AgentId INT
SET @.AgentsNames = ''
DECLARE curGetAgents CURSOR FOR
SELECT DISTINCT AgentId,ISNULL(c.FNAME,''),ISNULL(c.LNAME,'')
FROM tbl_customerToLocalAgent_Pass AS a
JOIN tbl_Customer AS b ON a.AgentId=b.CustomerId
JOIN tbl_CustomerDetails AS c ON b.CustomerId=c.CustomerId
AND b.PreferredLanguageId=c.LanguageId
WHERE a.CustomerId=@.CustomerId

--Opent the cursor
OPEN curGetAgents
--Fetch the first record
FETCH NEXT FROM curGetAgents INTO @.AgentId,@.AgentFName,@.AgentLName

WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
--Concatenate the names
SET @.AgentsNames = @.AgentsNames + ',' + @.AgentFName + ' ' + @.AgentLName
--Get the next row
FETCH NEXT FROM curGetAgents INTO @.AgentId,@.AgentFName,@.AgentLName
END
--Close cursor
CLOSE curGetAgents
DEALLOCATE curGetAgents

RETURN(RIGHT(@.AgentsNames,LEN(@.AgentsNames)))

END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Concatenate single quote...

Hi,
I am trying to concatenate single quotation mark (ie- ' ) in the select query but not succeed.
If I concatenate word India's, how should I have to write the select query..?
Its just query I have not concern with any specific database.select a='India''s', b='India'+'''s'

a b
--- ---
India's India's

(1 row(s) affected)|||Thanx buddy...

Concatenate Rows into Columns

I have a table 2 columns (Object and Weight) with data like below:

Object Weight(lb)
table 5
Chair 6
Computer 3
Computer 5
TV 20
TV 15
Radio 10
Computer 10

Question: I would like to create a new table with one column that would join the above two columns like below

Object
table 5
Chair 6
Computer 3, 5, 10
TV 20, 15
Radio 10

Thanks for your help in advance.

SELECT t3.Object, MAX(case t3.seq when 1 then t3.Weight end)

+ MAX(case t3.seq when 2 then ', ' + t3.Weight else '' end)

+ MAX(case t3.seq when 3 then ', ' + t3.Weight else '' end) AS Weight

FROM ( SELECT Object, Weight, (SELECT COUNT(*) FROM mergeTable1$ AS t2 WHERE t2.Object = t1.Object and t2.Weight <= t1.Weight) AS seq

FROM mergeTable1$ AS t1

) as t3

GROUP BY t3.Object

ORDER BY t3.Object

|||

What if I do not know the Max number of Weights for each object. eg. an object may have n number of weights in the table.

Thanks

|||

I would guess a maximum possible number in this solution. For example:

SELECT t3.Object, MAX(case t3.seq when 1 then t3.Weight end)

+ MAX(case t3.seq when 2 then ', ' + t3.Weight else '' end)

+ MAX(case t3.seq when 3 then ', ' + t3.Weight else '' end)

+ MAX(case t3.seq when 4 then ', ' + t3.Weight else '' end)

+ MAX(case t3.seq when 5 then ', ' + t3.Weight else '' end)

+ MAX(case t3.seq when 6 then ', ' + t3.Weight else '' end)

+ MAX(case t3.seq when 7 then ', ' + t3.Weight else '' end)

+ MAX(case t3.seq when 8 then ', ' + t3.Weight else '' end) AS Weight

FROM ( SELECT Object, Weight, (SELECT COUNT(*) FROM mergeTable1$ AS t2 WHERE t2.Object = t1.Object and t2.Weight <= t1.Weight) AS seq

FROM mergeTable1$ AS t1

) as t3

GROUP BY t3.Object

ORDER BY t3.Object

By the way, are you using SQL Server 2005? There are other solutions to handle this one.

|||

Yes, I am using sql server 2005

Thanks

|||

In SQL 2005, you can try like this

Create table tbl(Object varchar(100), Weight int)

insert tbl

Select 'table', 5 union all

Select 'Chair', 6 union all

Select 'Computer', 3 union all

Select 'Computer', 5 union all

Select 'TV' , 20 union all

Select 'TV' , 15 union all

Select 'Radio' , 10 union all

Select 'Computer' ,10

with CTE (Object,Weight1) as

(

select Object,Weight1=cast(Weight as varchar(50)) from tbl

union all

select a.Object,Weight1=convert(varchar(24),Weight1)+','+convert(varchar(25),a.Weight)

from tbl a inner loop join CTE b

on a.Object=b.Object and patindex('%'+convert(varchar(50),a.Weight)+'%',Weight1)<1

)

select distinct Object, max(Weight1) from CTE

Group by Object

|||

--We can use CTE in SQL Server 2005. The recursive function will take care of the number of records for the same object in your table.

With MyCTE(Object, Weight, Weights, myNum) AS

(SELECT a.Object, CONVERT(varchar(50), MIN(a.Weight)) as col1, CONVERT(varchar(50),(a.Weight)) as Weights, 1 as myNum

FROM mergeTable1$ a GROUP BY a.Object, CONVERT(varchar(50),(a.Weight))

UNION ALL

SELECT b.Object, CONVERT(varchar(50), b.Weight), CONVERT(varchar(50), (c.Weights + ',' + b.Weight)), c.myNum+1 as myNum

FROM mergeTable1$ b INNER JOIN MyCTE c ON b.Object=c.Object

WHERE b.Weight>c.Weight

)

SELECT a.Object, Weights FROM MyCTE a INNER JOIN (SELECT Max(a1.myNum) as myNumMax, a1.Object FROM MyCTE a1

group by a1.Object) b on a.Object=b.Object AND a.myNum= b.myNumMax ORDER BY a.Object

|||

Yet another way to do it, using data from one of the other posts, and the 2005 techniques from:

http://databases.aspfaq.com/general/how-do-i-concatenate-strings-from-a-column-into-a-single-row.html

Create table tbl(Object varchar(100), Weight int)

insert tbl

Select 'table', 5 union all

Select 'Chair', 6 union all

Select 'Computer', 3 union all

Select 'Computer', 5 union all

Select 'TV' , 20 union all

Select 'TV' , 15 union all

Select 'Radio' , 10 union all

Select 'Computer' ,10

SELECT
Object,
Weights = LEFT(o.list, LEN(o.list)-1)
FROM
(select distinct object from tbl) as tbl --if these values are defined in another related table it is more clear
CROSS APPLY
(
SELECT CONVERT(VARCHAR(12), Weight) + ',' AS [text()]
FROM tbl as t
WHERE t.object = tbl.object
ORDER BY weight
FOR XML PATH('')
) o (list)

|||If you don't mind, what does MYCTE stand for ?|||CTE (Common Table Expression) is a new function in SQL Server 2005. You can look it up from Book Online. MyCTE is a name like you would call a table mytable in your database. I like Louis's solution a lot. It uses another new CROSS APPLY funtion in SQL Server 2005 too.|||

For some ideas, see:

http://www.projectdmx.com/tsql/rowconcatenate.aspx

Anith

|||

i belive this can help

well use this function to get retrive the string that cotaians the rows of the specific ID.

CREATE FUNCTION dbo.ConRow(@.JID int)

RETURNS VARCHAR(8000)

BEGIN

DECLARE @.Output VARCHAR(8000)

SELECT @.Output = COALESCE(@.Output+', ', '') + CONVERT(varchar(20), JP.a)

FROM [E_JobPending] JP

WHERE JP.JobID = @.JID

RETURN @.Output

END

select dbo.ConRow(jobid), vE_Job.*

from

vE_Job

DROP FUNCTION dbo.ConRow

Concatenate Rows into Columns

I have a table 2 columns (Object and Weight) with data like below:

Object Weight(lb)
table 5
Chair 6
Computer 3
Computer 5
TV 20
TV 15
Radio 10
Computer 10

Question: I would like to create a new table with one column that would join the above two columns like below

Object
table 5
Chair 6
Computer 3, 5, 10
TV 20, 15
Radio 10

Thanks for your help in advance.

SELECT t3.Object, MAX(case t3.seq when 1 then t3.Weight end)

+ MAX(case t3.seq when 2 then ', ' + t3.Weight else '' end)

+ MAX(case t3.seq when 3 then ', ' + t3.Weight else '' end) AS Weight

FROM ( SELECT Object, Weight, (SELECT COUNT(*) FROM mergeTable1$ AS t2 WHERE t2.Object = t1.Object and t2.Weight <= t1.Weight) AS seq

FROM mergeTable1$ AS t1

) as t3

GROUP BY t3.Object

ORDER BY t3.Object

|||

What if I do not know the Max number of Weights for each object. eg. an object may have n number of weights in the table.

Thanks

|||

I would guess a maximum possible number in this solution. For example:

SELECT t3.Object, MAX(case t3.seq when 1 then t3.Weight end)

+ MAX(case t3.seq when 2 then ', ' + t3.Weight else '' end)

+ MAX(case t3.seq when 3 then ', ' + t3.Weight else '' end)

+ MAX(case t3.seq when 4 then ', ' + t3.Weight else '' end)

+ MAX(case t3.seq when 5 then ', ' + t3.Weight else '' end)

+ MAX(case t3.seq when 6 then ', ' + t3.Weight else '' end)

+ MAX(case t3.seq when 7 then ', ' + t3.Weight else '' end)

+ MAX(case t3.seq when 8 then ', ' + t3.Weight else '' end) AS Weight

FROM ( SELECT Object, Weight, (SELECT COUNT(*) FROM mergeTable1$ AS t2 WHERE t2.Object = t1.Object and t2.Weight <= t1.Weight) AS seq

FROM mergeTable1$ AS t1

) as t3

GROUP BY t3.Object

ORDER BY t3.Object

By the way, are you using SQL Server 2005? There are other solutions to handle this one.

|||

Yes, I am using sql server 2005

Thanks

|||

In SQL 2005, you can try like this

Create table tbl(Object varchar(100), Weight int)

insert tbl

Select 'table', 5 union all

Select 'Chair', 6 union all

Select 'Computer', 3 union all

Select 'Computer', 5 union all

Select 'TV' , 20 union all

Select 'TV' , 15 union all

Select 'Radio' , 10 union all

Select 'Computer' ,10

with CTE (Object,Weight1) as

(

select Object,Weight1=cast(Weight as varchar(50)) from tbl

union all

select a.Object,Weight1=convert(varchar(24),Weight1)+','+convert(varchar(25),a.Weight)

from tbl a inner loop join CTE b

on a.Object=b.Object and patindex('%'+convert(varchar(50),a.Weight)+'%',Weight1)<1

)

select distinct Object, max(Weight1) from CTE

Group by Object

|||

--We can use CTE in SQL Server 2005. The recursive function will take care of the number of records for the same object in your table.

With MyCTE(Object, Weight, Weights, myNum) AS

(SELECT a.Object, CONVERT(varchar(50), MIN(a.Weight)) as col1, CONVERT(varchar(50),(a.Weight)) as Weights, 1 as myNum

FROM mergeTable1$ a GROUP BY a.Object, CONVERT(varchar(50),(a.Weight))

UNION ALL

SELECT b.Object, CONVERT(varchar(50), b.Weight), CONVERT(varchar(50), (c.Weights + ',' + b.Weight)), c.myNum+1 as myNum

FROM mergeTable1$ b INNER JOIN MyCTE c ON b.Object=c.Object

WHERE b.Weight>c.Weight

)

SELECT a.Object, Weights FROM MyCTE a INNER JOIN (SELECT Max(a1.myNum) as myNumMax, a1.Object FROM MyCTE a1

group by a1.Object) b on a.Object=b.Object AND a.myNum= b.myNumMax ORDER BY a.Object

|||

Yet another way to do it, using data from one of the other posts, and the 2005 techniques from:

http://databases.aspfaq.com/general/how-do-i-concatenate-strings-from-a-column-into-a-single-row.html

Create table tbl(Object varchar(100), Weight int)

insert tbl

Select 'table', 5 union all

Select 'Chair', 6 union all

Select 'Computer', 3 union all

Select 'Computer', 5 union all

Select 'TV' , 20 union all

Select 'TV' , 15 union all

Select 'Radio' , 10 union all

Select 'Computer' ,10

For some ideas, see:

http://www.projectdmx.com/tsql/rowconcatenate.aspx

Anith

|||

i belive this can help

well use this function to get retrive the string that cotaians the rows of the specific ID.

CREATE FUNCTION dbo.ConRow(@.JID int)

RETURNS VARCHAR(8000)

BEGIN

DECLARE @.Output VARCHAR(8000)

SELECT @.Output = COALESCE(@.Output+', ', '') + CONVERT(varchar(20), JP.a)

FROM [E_JobPending] JP

WHERE JP.JobID = @.JID

RETURN @.Output

END

select dbo.ConRow(jobid), vE_Job.*

from

vE_Job

DROP FUNCTION dbo.ConRow

Concatenate Rows

I have a table similar to the following:

Date ID Name Job Number JobType

12/12/2007 123456 Fred Smith 111111 Full Day

12/12/2007 654321 Bob Blue 222222 Half Day AM

12/12/2007 654321 Bob Blue 333333 Half Day PM

I need the following output:

Date ID Name Job Number JobType

12/12/2007 123456 Fred Smith 111111 Full Day

12/12/2007 654321 Bob Blue 222222 Half Day AM

12/12/2007 654321 Bob Blue 333333 Half Day PM

Now before you say the output is the same . It isn't! There are only 2 records in the output. The italic lines are one record, with a carriage return linefeed between each piece of data. So for job number the field is equal to 111111 + CHAR(10) + CHAR(13) + 222222

Could someone please point me in the right direction?

Cheers

You could to use SELECT FOR XML PAHT with empty tag:

Code Snippet

create table t2

(

Date datetime,

ID int,

Name varchar(20),

JobNumber varchar(20),

JobType varchar(20)

)

insert into t2 values('12/12/2007', 123456,'Fred Smith','111111','Full Day')

insert into t2 values('12/12/2007', 654321,'Bob Blue',' 222222','Half Day AM')

insert into t2 values('12/12/2007', 654321,'Bob Blue',' 333333','Half Day PM')

select

replace( (SELECT name + '##' FROM t2 as d where d.ID=m.ID FOR XML PATH('')), '##', char(10)+char(13) ) as CName

,ID from t2 m group by ID

|||Hi Kosinsky,
Your querry is not working in SQL200 is it for SQL 2005 or it will run properly in sql2000 also if not then wht will be the querry for sql2000,

I got the following error when i am trying to run your select querry in sql2000

Code Snippet

Server: Msg 170, Level 15, State 1, Line 1Line 1:

Incorrect syntax near 'XML'.

|||

My query use SELECT FOR XML PATH. Its SQL Server 2005 feature.

For SQL Server 2000 you could use FOR XML RAW and two additional replaces:

Code Snippet

select

replace

(

replace

(

replace( (SELECT name as t FROM t2 as d where d.ID=m.ID FOR XML RAW('t')), '"/><t t="', char(10)+char(13))

,'<t t="',''

)

,'"/>',''

)

,ID

from t2 m group by ID

|||Hi,
Still its giving me the same error.?

Code Snippet

Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'XML'.

|||

Sorry, but me solution doesn't work on SQL Server 2000. Because FOR XML is not valid in subselections

|||

Thanks for the replies Konstantin Kosinsky, but I'm also running SQL Server 2000.

Does anyone have any other ideas on how to achieve this please?

Cheers

|||

From what I can tell, all the easy solutions for this are in SQL2005. SQL2000 solutions are much messier. Try searching this forum for words like aggregate and concatenate. There are a few that might help, like this one:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=125302&SiteID=1

Note Umachandar's solution I think should work on 2000. MRys', while much neater, relies on having 2005.

Good luck.|||

Cheers Cringing Dragon, great find. I used the post on the link you provided by Umachandar Jayachandran - MS.

The SQL of which is:

Code Snippet

select t3.id
, substring(
max(case t3.seq when 1 then ',' + t3.comment else '' end)
+ max(case t3.seq when 2 then ',' + t3.comment else '' end)
+ max(case t3.seq when 3 then ',' + t3.comment else '' end)
+ max(case t3.seq when 4 then ',' + t3.comment else '' end)
+ max(case t3.seq when 5 then ',' + t3.comment else '' end)
, 2, 8000) as comments
-- put as many MAX expressions as you expect items for each id
from (
select t1.id, t1.comment, count(*) as seq
from your_table as t1
join your_table as t2
on t2.id = t1.id and t2.comment <= t1.comment
group by t1.id, t1.comment
) as t3
group by t3.id;

Thank you all for your help.

Concatenate Rows

I have a table similar to the following:

Date ID Name Job Number JobType

12/12/2007 123456 Fred Smith 111111 Full Day

12/12/2007 654321 Bob Blue 222222 Half Day AM

12/12/2007 654321 Bob Blue 333333 Half Day PM

I need the following output:

Date ID Name Job Number JobType

12/12/2007 123456 Fred Smith 111111 Full Day

12/12/2007 654321 Bob Blue 222222 Half Day AM

12/12/2007 654321 Bob Blue 333333 Half Day PM

Could someone please point me in the right direction?

Cheers

You could to use SELECT FOR XML PAHT with empty tag:

Code Snippet

create table t2

(

Date datetime,

ID int,

Name varchar(20),

JobNumber varchar(20),

JobType varchar(20)

)

insert into t2 values('12/12/2007', 123456,'Fred Smith','111111','Full Day')

insert into t2 values('12/12/2007', 654321,'Bob Blue',' 222222','Half Day AM')

insert into t2 values('12/12/2007', 654321,'Bob Blue',' 333333','Half Day PM')

select

replace( (SELECT name + '##' FROM t2 as d where d.ID=m.ID FOR XML PATH('')), '##', char(10)+char(13) ) as CName

,ID from t2 m group by ID

|||Hi Kosinsky,
Your querry is not working in SQL200 is it for SQL 2005 or it will run properly in sql2000 also if not then wht will be the querry for sql2000,

I got the following error when i am trying to run your select querry in sql2000

Code Snippet

Server: Msg 170, Level 15, State 1, Line 1Line 1:

Incorrect syntax near 'XML'.

|||

My query use SELECT FOR XML PATH. Its SQL Server 2005 feature.

For SQL Server 2000 you could use FOR XML RAW and two additional replaces:

Code Snippet

select

replace

(

replace

(

replace( (SELECT name as t FROM t2 as d where d.ID=m.ID FOR XML RAW('t')), '"/><t t="', char(10)+char(13))

,'<t t="',''

)

,'"/>',''

)

,ID

from t2 m group by ID

|||Hi,
Still its giving me the same error.?

Code Snippet

Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'XML'.

|||

Sorry, but me solution doesn't work on SQL Server 2000. Because FOR XML is not valid in subselections

|||

Thanks for the replies Konstantin Kosinsky, but I'm also running SQL Server 2000.

Does anyone have any other ideas on how to achieve this please?

Cheers

|||

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=125302&SiteID=1

Note Umachandar's solution I think should work on 2000. MRys', while much neater, relies on having 2005.

Good luck.|||

Cheers Cringing Dragon, great find. I used the post on the link you provided by Umachandar Jayachandran - MS.

The SQL of which is:

Code Snippet

Thank you all for your help.

Concatenate Query Results into a String most effectively

Hi all,
I want to concatenate the results from a query in a string in the most
performance-friendly manner. E.g. executing
SELECT Code, Price
FROM Inventory
returns
ABC, 120
EFG, 200
HIJ, 245.50
...
Instead of returning the result in the format above, I want it to look like
this
ABC, 120/ EFG, 200/ HIJ, 245.50/ ...
returned as a string, or a column.
The normal way is to use cursor, and loop all the rows and concatenate the
values in each row into the string. BUT I read cursors are not
performance-friendly.
So what is the most performance-friendly SQL statement(s) that I can write
(preferably in one statement) to do the above?
Please kindly advise. TQ very much in advance.Hi
Why not doung such things on the client side'?
CREATE TABLE #Test
(
col CHAR(1)NOT NULL,
col1 DECIMAL(5,2) NOT NULL
)
INSERT INTO #Test VALUES('A',120)
INSERT INTO #Test VALUES('B',155)
INSERT INTO #Test VALUES('C',20.33)
DECLARE @.st VARCHAR(100)
SET @.st=''
SELECT @.st=@.st+col+','+CAST(col1 AS VARCHAR(10))+'/' FROM #Test
SELECT @.st
"HardKhor" <HardKhor@.discussions.microsoft.com> wrote in message
news:F18E50B2-F6D0-4FA8-BC2D-2242935F01FE@.microsoft.com...
> Hi all,
> I want to concatenate the results from a query in a string in the most
> performance-friendly manner. E.g. executing
> SELECT Code, Price
> FROM Inventory
> returns
> ABC, 120
> EFG, 200
> HIJ, 245.50
> ...
> Instead of returning the result in the format above, I want it to look
like
> this
> ABC, 120/ EFG, 200/ HIJ, 245.50/ ...
> returned as a string, or a column.
> The normal way is to use cursor, and loop all the rows and concatenate the
> values in each row into the string. BUT I read cursors are not
> performance-friendly.
> So what is the most performance-friendly SQL statement(s) that I can write
> (preferably in one statement) to do the above?
> Please kindly advise. TQ very much in advance.|||Hi,
Thanks. Cool solution. Never think abt that!
"Uri Dimant" wrote:

> Hi
> Why not doung such things on the client side'?
Well I am customizing an existing program... So try to keep my changes
minimum and yet still get good perforamance. ;)
Furthermore doing such things on client side would mean I have to write
additional VB codes and Crystal Report to reformat the whole thing. SQL
statements still so much more powerful.

>
> CREATE TABLE #Test
> (
> col CHAR(1)NOT NULL,
> col1 DECIMAL(5,2) NOT NULL
> )
> INSERT INTO #Test VALUES('A',120)
> INSERT INTO #Test VALUES('B',155)
> INSERT INTO #Test VALUES('C',20.33)
> DECLARE @.st VARCHAR(100)
> SET @.st=''
> SELECT @.st=@.st+col+','+CAST(col1 AS VARCHAR(10))+'/' FROM #Test
> SELECT @.st
>
>
> "HardKhor" <HardKhor@.discussions.microsoft.com> wrote in message
> news:F18E50B2-F6D0-4FA8-BC2D-2242935F01FE@.microsoft.com...
> like
>
>

Concatenate problem.

Using SQL Server 2000 and trying to generate sql:
sp_password null, 'password', 'name'
selecting from sysxlogins but cannot get past error msg 'Invalid operator
for data type. Operator equals add, type equals nvarchar. I have tried
convert and cast and am aware of precedence order but to no avail. This
should be simple I thought.
Any help would be appreciated.
--
Tim - DBAHi
You example is exactly the same as the example in BOL. Conversion from
varchar to a sysname should be implicit. Therefore it is probably something
else that is giving the error message such as an unescaped apostrophy.
You could try adding an exec in front of each procedure call and printing
out the statements you are executing so that you can run them in Query
Analyser.
John
"Tim" wrote:
> Using SQL Server 2000 and trying to generate sql:
> sp_password null, 'password', 'name'
> selecting from sysxlogins but cannot get past error msg 'Invalid operator
> for data type. Operator equals add, type equals nvarchar. I have tried
> convert and cast and am aware of precedence order but to no avail. This
> should be simple I thought.
> Any help would be appreciated.
> --
> Tim - DBA|||Are you going to show us the query you were using?
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Tim" <Tim@.discussions.microsoft.com> wrote in message
news:49B44438-7868-4B95-8B6C-54D9B6FE6AE5@.microsoft.com...
> Using SQL Server 2000 and trying to generate sql:
> sp_password null, 'password', 'name'
> selecting from sysxlogins but cannot get past error msg 'Invalid operator
> for data type. Operator equals add, type equals nvarchar. I have tried
> convert and cast and am aware of precedence order but to no avail. This
> should be simple I thought.
> Any help would be appreciated.
> --
> Tim - DBA|||Here is basic query minus quotes and spaces etc.
select password + name from sysxlogins
Tim - DBA
"John Bell" wrote:
> Hi
> You example is exactly the same as the example in BOL. Conversion from
> varchar to a sysname should be implicit. Therefore it is probably something
> else that is giving the error message such as an unescaped apostrophy.
> You could try adding an exec in front of each procedure call and printing
> out the statements you are executing so that you can run them in Query
> Analyser.
> John
>
> "Tim" wrote:
> > Using SQL Server 2000 and trying to generate sql:
> > sp_password null, 'password', 'name'
> > selecting from sysxlogins but cannot get past error msg 'Invalid operator
> > for data type. Operator equals add, type equals nvarchar. I have tried
> > convert and cast and am aware of precedence order but to no avail. This
> > should be simple I thought.
> > Any help would be appreciated.
> >
> > --
> > Tim - DBA|||Hi
Try the following (untested)
DECLARE @.cmd nvarchar(200), @.ParmDefinition nvarchar(200)
DECLARE @.password sysname, @.newpassword sysname, @.name sysname
SET @.ParmDefinition = N'@.pwd sysname, @.newpwd sysname, @.loginnm sysname'
SET @.cmd = 'sp_password @.old = @.pwd, @.new = @.newpwd, @.loginname = @.loginnm'
SET @.newpassword = 'unsecure'
SELECT @.password = password, @.name = name
FROM sysxlogins
WHERE name = 'mylogin'
EXEC sp_executesql @.stmt = @.cmd , @.params = @.ParmDefinition, @.pwd =@.password, @.newpwd = @.newpassword, @.loginnm = @.name
If you want to do multiple rows from sysclogins you will need to use a
cursor and call sp_executesql for each iteration.
John
"Tim" wrote:
> Here is basic query minus quotes and spaces etc.
> select password + name from sysxlogins
>
> --
> Tim - DBA
>
> "John Bell" wrote:
> > Hi
> >
> > You example is exactly the same as the example in BOL. Conversion from
> > varchar to a sysname should be implicit. Therefore it is probably something
> > else that is giving the error message such as an unescaped apostrophy.
> >
> > You could try adding an exec in front of each procedure call and printing
> > out the statements you are executing so that you can run them in Query
> > Analyser.
> >
> > John
> >
> >
> >
> > "Tim" wrote:
> >
> > > Using SQL Server 2000 and trying to generate sql:
> > > sp_password null, 'password', 'name'
> > > selecting from sysxlogins but cannot get past error msg 'Invalid operator
> > > for data type. Operator equals add, type equals nvarchar. I have tried
> > > convert and cast and am aware of precedence order but to no avail. This
> > > should be simple I thought.
> > > Any help would be appreciated.
> > >
> > > --
> > > Tim - DBA

Concatenate problem.

Using SQL Server 2000 and trying to generate sql:
sp_password null, 'password', 'name'
selecting from sysxlogins but cannot get past error msg 'Invalid operator
for data type. Operator equals add, type equals nvarchar. I have tried
convert and cast and am aware of precedence order but to no avail. This
should be simple I thought.
Any help would be appreciated.
Tim - DBAAre you going to show us the query you were using?
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Tim" <Tim@.discussions.microsoft.com> wrote in message
news:49B44438-7868-4B95-8B6C-54D9B6FE6AE5@.microsoft.com...
> Using SQL Server 2000 and trying to generate sql:
> sp_password null, 'password', 'name'
> selecting from sysxlogins but cannot get past error msg 'Invalid operator
> for data type. Operator equals add, type equals nvarchar. I have tried
> convert and cast and am aware of precedence order but to no avail. This
> should be simple I thought.
> Any help would be appreciated.
> --
> Tim - DBA|||Hi
You example is exactly the same as the example in BOL. Conversion from
varchar to a sysname should be implicit. Therefore it is probably something
else that is giving the error message such as an unescaped apostrophy.
You could try adding an exec in front of each procedure call and printing
out the statements you are executing so that you can run them in Query
Analyser.
John
"Tim" wrote:

> Using SQL Server 2000 and trying to generate sql:
> sp_password null, 'password', 'name'
> selecting from sysxlogins but cannot get past error msg 'Invalid operator
> for data type. Operator equals add, type equals nvarchar. I have tried
> convert and cast and am aware of precedence order but to no avail. This
> should be simple I thought.
> Any help would be appreciated.
> --
> Tim - DBA|||Here is basic query minus quotes and spaces etc.
select password + name from sysxlogins
Tim - DBA
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> You example is exactly the same as the example in BOL. Conversion from
> varchar to a sysname should be implicit. Therefore it is probably somethin
g
> else that is giving the error message such as an unescaped apostrophy.
> You could try adding an exec in front of each procedure call and printing
> out the statements you are executing so that you can run them in Query
> Analyser.
> John
>
> "Tim" wrote:
>|||Hi
Try the following (untested)
DECLARE @.cmd nvarchar(200), @.ParmDefinition nvarchar(200)
DECLARE @.password sysname, @.newpassword sysname, @.name sysname
SET @.ParmDefinition = N'@.pwd sysname, @.newpwd sysname, @.loginnm sysname'
SET @.cmd = 'sp_password @.old = @.pwd, @.new = @.newpwd, @.loginname = @.loginnm'
SET @.newpassword = 'unsecure'
SELECT @.password = password, @.name = name
FROM sysxlogins
WHERE name = 'mylogin'
EXEC sp_executesql @.stmt = @.cmd , @.params = @.ParmDefinition, @.pwd =
@.password, @.newpwd = @.newpassword, @.loginnm = @.name
If you want to do multiple rows from sysclogins you will need to use a
cursor and call sp_executesql for each iteration.
John
"Tim" wrote:
[vbcol=seagreen]
> Here is basic query minus quotes and spaces etc.
> select password + name from sysxlogins
>
> --
> Tim - DBA
>
> "John Bell" wrote:
>sql sql

concatenate problem

Hi All,

I am facing problem when i try to concatenate two columns. I have text in one column and numeric data in other field, and in want to update field with text datatype and wants to put '-field2'(field with numeic data) as suffix in text data field.

Result should be "field1-field2"

Any help will be appericiated

ThanksOriginally posted by Devinder Gera
Hi All,

I am facing problem when i try to concatenate two columns. I have text in one column and numeric data in other field, and in want to update field with text datatype and wants to put '-field2'(field with numeic data) as suffix in text data field.

Result should be "field1-field2"

Any help will be appericiated

Thanks
If I read that right you want:

Select field1 + convert(varchar, field2)

You can use that in an INSERT or UPDATE statement.

HTH, Saint|||If I read that right you want:

Select field1 + convert(varchar, field2)

You can use that in an INSERT or UPDATE statement.

HTH, Saint [/SIZE][/QUOTE]

Hi Saint,

Thanks for your reply. I tried this but it works fine in select statement but in update it gives different results.

Following is result in select statement and thats what i want after update
07535494187886-1
07535494187886-2
07535494187886-3
07535494187886-4
30834281804606-1
09462976809022-1
09462976809022-2
37882735916006-1

But actually after update i am getting following result

07535494187886-1-1-1-1-1-1-1-1
07535494187886-2-2-2-2-2-2-2
07535494187886-3-3-3-3-3-3
07535494187886-4-4-4-4-4
30834281804606-1-1-1-1
09462976809022-1-1-1
09462976809022-2-2
37882735916006-1

Any idea why its so.

Thanks|||Originally posted by Devinder Gera
If I read that right you want:

Select field1 + convert(varchar, field2)

You can use that in an INSERT or UPDATE statement.

HTH, Saint

Hi Saint,

Thanks for your reply. I tried this but it works fine in select statement but in update it gives different results.

Following is result in select statement and thats what i want after update
07535494187886-1
07535494187886-2
07535494187886-3
07535494187886-4
30834281804606-1
09462976809022-1
09462976809022-2
37882735916006-1

But actually after update i am getting following result

07535494187886-1-1-1-1-1-1-1-1
07535494187886-2-2-2-2-2-2-2
07535494187886-3-3-3-3-3-3
07535494187886-4-4-4-4-4
30834281804606-1-1-1-1
09462976809022-1-1-1
09462976809022-2-2
37882735916006-1

Any idea why its so.

Thanks [/SIZE][/QUOTE]

No worries guys it started working. I was just updating at wrong time. I changed the location of update now its working fantastic

Thanks a million Saint

concatenate nulls in SQL server

I have a problem with a view in MS SQL Server 2000. The View concatenates 3 fields (prefix, partnumber, suffix) using hte "+" operator and one or more of these fields may sometimes be null. The problem is that a null value in any of the three fields causes the concatenation to return null even if there are valid values in on or both of the other fields. I thought I might be able to work around this by creating a view containing CASE statements to render the null values as zero length strings, which concatenate properly (e.g., "SELECT CASE WHEN PREFIX IS NULL THEN '' ELSE PREFIX END 'prefix2' FROM [table_name]"). But SQL server will not let me save a view containing a CASE statement. Anybody know how to resolve this problem?

By the way, I also tried to use UNION views to work around this but SQL server 2000 will not let me save views with UNION sataments even though it runs them properly when views with UNION statements that were created in SQL server 7.0 are imported. What's up with the inability to save a view just because it can't be rendered in the gui pane of the query builder??select isnull(prefix,'')+isnull(partnumber,'')+isnull(suf fix,'') from your table|||I know you can use a function in a view, so perhaps you can write a function that takes as input the three values and internally builds the string using your case logic (or, alternatively, using ISNULL, as in:

SET @.outputstring = ISNULL(@.value1,'') + ISNULL(value2,'') + ISNULL(value3,'')

then just use the function within the view's select statement.|||you can use the function coalesce.

select coalesce(prefix, '') + coalesce(partnumber, '') + coalesce(suffix, '')
from your_table|||Thanks for the help. The isnull concatenation works so my (current) problem is resolved. I still don't know why I can no longer save views with UNION statements in them in SQL 2000...|||Why are you creating queries in the GUI query builder? You mean the one in Enterprise Manager? It generates crappy code. Plus I don't think you can save the resulting queries as views directly anyway. It's not designed for creating objects, but for viewing data. You can paste the code it creates into Query Analyzer to create your Union query, but clean it up first.|||I've often wondered about that, I have done some stuff in views (ordering, is one that leaps to mind) that the enterprise manager screams about, but when I do it through SQL Analyzer, it allows it, and the resulting view SEEMS to work flawlessly...what's UP with that? Is Uncle Billy just trying to save us from ourselves or something?|||You can't ORDER a view unless you include the TOP clause, so it may have screamed about that.

The GUI "designer" in Enterprise Manager really is only for viewing data. And you have to be carefull even then, 'cause under some circumstances you can accidently change data in the resultset and end up modifying the source data. I generally avoid EM's GUI.

Concatenate nuimbers

I have two fields that have numbers company and employee, I want to
concatenate these numbers so it it takes the comapny number (9000) and
employee number (116258) and makes 9000116258.
When i use this query
(select company+employee as uid from cr_staging) it adds the numbers.
I also tried
SELECT Company, Employee, Company & ' ' & Employee AS uid
FROM cr_staging
but it gives me an error invalid operator for data type.
Is there any suggestions on this problem?
Thanks,
This will work:
select CONVERT(varchar(10),company) + convert(varchar(10),employee) from
cr_staging
"Eric" wrote:

> I have two fields that have numbers company and employee, I want to
> concatenate these numbers so it it takes the comapny number (9000) and
> employee number (116258) and makes 9000116258.
> When i use this query
> (select company+employee as uid from cr_staging) it adds the numbers.
> I also tried
> SELECT Company, Employee, Company & ' ' & Employee AS uid
> FROM cr_staging
> but it gives me an error invalid operator for data type.
> Is there any suggestions on this problem?
>
> Thanks,
>
|||+ adds numbers when they are numbers (int, numeric...)
+ combines varchar data
You need to convert the columns to varchar before you "add" them.
SELECT CONVERT(varchar(20),company) + CONVERT(varchar(20),employee)
FROM cr_staging
Keith
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:9862CFBF-3B7A-43B0-9A33-136F2B228A05@.microsoft.com...
> I have two fields that have numbers company and employee, I want to
> concatenate these numbers so it it takes the comapny number (9000) and
> employee number (116258) and makes 9000116258.
> When i use this query
> (select company+employee as uid from cr_staging) it adds the numbers.
> I also tried
> SELECT Company, Employee, Company & ' ' & Employee AS uid
> FROM cr_staging
> but it gives me an error invalid operator for data type.
> Is there any suggestions on this problem?
>
> Thanks,
>
|||Assuming that your fields are defined as INT and that you want to keep the
same number of digits (4 + 6), try this:
select
(
RIGHT('0000' + CAST(Company AS varchar(4)), 4)
+
RIGHT('000000' + CAST(Employee AS varchar(6)), 6)
)
as UID
from cr_staging
This will give you the result: '9000116258'
HTH,
Robert

Concatenate nuimbers

I have two fields that have numbers company and employee, I want to
concatenate these numbers so it it takes the comapny number (9000) and
employee number (116258) and makes 9000116258.
When i use this query
(select company+employee as uid from cr_staging) it adds the numbers.
I also tried
SELECT Company, Employee, Company & ' ' & Employee AS uid
FROM cr_staging
but it gives me an error invalid operator for data type.
Is there any suggestions on this problem?
Thanks,This will work:
select CONVERT(varchar(10),company) + convert(varchar(10),employee) from
cr_staging
"Eric" wrote:
> I have two fields that have numbers company and employee, I want to
> concatenate these numbers so it it takes the comapny number (9000) and
> employee number (116258) and makes 9000116258.
> When i use this query
> (select company+employee as uid from cr_staging) it adds the numbers.
> I also tried
> SELECT Company, Employee, Company & ' ' & Employee AS uid
> FROM cr_staging
> but it gives me an error invalid operator for data type.
> Is there any suggestions on this problem?
>
> Thanks,
>|||+ adds numbers when they are numbers (int, numeric...)
+ combines varchar data
You need to convert the columns to varchar before you "add" them.
SELECT CONVERT(varchar(20),company) + CONVERT(varchar(20),employee)
FROM cr_staging
--
Keith
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:9862CFBF-3B7A-43B0-9A33-136F2B228A05@.microsoft.com...
> I have two fields that have numbers company and employee, I want to
> concatenate these numbers so it it takes the comapny number (9000) and
> employee number (116258) and makes 9000116258.
> When i use this query
> (select company+employee as uid from cr_staging) it adds the numbers.
> I also tried
> SELECT Company, Employee, Company & ' ' & Employee AS uid
> FROM cr_staging
> but it gives me an error invalid operator for data type.
> Is there any suggestions on this problem?
>
> Thanks,
>|||Assuming that your fields are defined as INT and that you want to keep the
same number of digits (4 + 6), try this:
select
(
RIGHT('0000' + CAST(Company AS varchar(4)), 4)
+
RIGHT('000000' + CAST(Employee AS varchar(6)), 6)
)
as UID
from cr_staging
This will give you the result: '9000116258'
HTH,
Robert

Concatenate Multiple Rows?

I can't figure out how to write an SQL query that concatenates one field from a list of records having other parameters in common. It is easier to show than explain. Take the data set:

Spec T_R Section
A008 23w 1
A008 23w 2
A008 23w 4

I need a query that returns a single record/row like this:

Spec T_R Section
A008 23w 1, 2, 4

Any help would be appreciated.I've had this problem more times than I can count. While I was writing my SQL Tutorial (http://www.bitesizeinc.net/index.php/sql.html), I ran across this function for MySQL :

group_concat(field)

Which concatenates the grouped results into a string. If you are using Oracle, you'll need a stored procedure...

-Chrissql sql

Concatenate multiple reports into one pdf

Is there any way to do the above? I need to supply a number of "management accounts" reports individually during the month, and then the accountant needs to run them all into a single pdf at the end of the month for the board report.

Thanks in advance

Combining reports into one report directly is not a current feature. One option you could try would be to create a master report that uses the Sub Report feature of RS. A simplistic view would be to create the master report that looks like Subreport\Pagebreak\Subreport\Pagebreak\etc...

|||Thanks - I had a feeling that was the case. I have lots of info in the header, so I will have to strip that out.

Concatenate Multiple Records Into One Field

Someone please help!. I am trying to create a view in SQL Server 2000 to use for a report but I am having problems with concatenating multiple values into one field. In my example below I am trying to list all records in my queried table in columnA then concatenate a list of all other records that share the same value in column B of the queried table into another field. If there are no other matches for a row in columnA then I would leave the corresponding field in columnB blank. Thanks in advance.

TABLE
ColumnA ColumnB
1......A
2......B
3......C
4......A
5......A
6......B
7......C
8......D
9......C
10.....E

EXPECTED OUTPUT
ColumnA ColumnB
1......4,5
2......6
3......7
4......1,5
5......1, 4
6......2
7......3
8......
9......3,7
10.....create function ConcatFld (@.RowId int, @.RowVal char(1))
returns varchar(100) AS
begin
declare @.Ret varchar(100)
set @.Ret=''
select @.Ret= @.Ret + cast(ColA as varchar)+',' from Tbl1 where ColB=@.RowVal and ColA<>@.RowId
if len(@.Ret) > 0
set @.Ret = left(@.Ret,len(@.Ret)-1)
return @.Ret
end
--------

select ColA, dbo.ConcatFld(ColA,ColB) from Tbl1|||Thanks Upalsen, this is exactly what I need!!!

concatenate multiple fields

I don't have any troubles concatenating two fields together but so far, I
haven't been able to concatenate more than two fields.
(Order.FirstName+' '+order.LastName) as Name
will work
(Order.FirstName+' '+ order.MiddleName+ ' '+order.LastName) as Name
will not work.
Any suggestions?I can't think of any reason that shouldn't work.
What is the exact error?
Is this a problem you see in Query Analyzer or
via your "client" code such as ASP or ASP.NET?
2004 and 2005 Microsoft MVP C#
Robbe Morris
http://www.masterado.net
Earn $$$ money answering .NET Framework
messageboard posts at EggHeadCafe.com.
http://www.eggheadcafe.com/forums/merit.asp
"jeff fisher" <jeff@.fisher.com> wrote in message
news:eWY6$uvlFHA.3120@.TK2MSFTNGP09.phx.gbl...
>I don't have any troubles concatenating two fields together but so far, I
> haven't been able to concatenate more than two fields.
> (Order.FirstName+' '+order.LastName) as Name
> will work
> (Order.FirstName+' '+ order.MiddleName+ ' '+order.LastName) as Name
> will not work.
> Any suggestions?|||If by "not work" you mean the seconds example is NULL, then it's because the
middle name field is NULL -- concatenating any char field with NULL yeilds
NULL; mathmatical, bitwise, and other operations have similar behavior.
Anyways this is a display issue and would be better handled by your client
code, but for some solutions, in BOL look up:
- ISNULL
- COALESCE
- SET CONCAT NULL YIELDS NULL
"jeff fisher" wrote:

> I don't have any troubles concatenating two fields together but so far, I
> haven't been able to concatenate more than two fields.
> (Order.FirstName+' '+order.LastName) as Name
> will work
> (Order.FirstName+' '+ order.MiddleName+ ' '+order.LastName) as Name
> will not work.
> Any suggestions?
>|||Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files.
What the heck does "will not work" mean' Would you like to go to a
doctor that tells you something like that? Why did you use a reserved
word for a table name'
My guess -- based on absolutely nothing you told us -- is that you
have NULL-able columns and do not know that NULLs propagate, one of the
most basic priniciples in SQL.

Concatenate Lname and Fname Columns

I'm using Access 2002 and Sql 2000.
I have a Lname and Fname columns that I'm try to concatenate. I'm trying to
use Lname + ', ' + Fname in a View. But Lname is the things that displays.
What am I doing wrong?
Thanks for the help,
Paulpjscott wrote:
> I'm using Access 2002 and Sql 2000.
> I have a Lname and Fname columns that I'm try to concatenate. I'm
> trying to use Lname + ', ' + Fname in a View. But Lname is the things
> that displays.
> What am I doing wrong?
> Thanks for the help,
> Paul
Are you using fixed-length character columns? If so, you'll need to trim
the data. For example:
create table #Names (
LName1 CHAR(20),
LName2 VARCHAR(20),
FName1 CHAR(20),
FName2 VARCHAR(20) )
go
Insert Into #Names Values (
'Gugick', 'Gugick','David','David')
Insert Into #Names Values (
'Smith', 'Smith','Dan','Dan')
go
Select
LName1 + ', ' + FName1 as "Fixed-Length Name",
LName2 + ', ' + FName2 as "Variable-Length Name",
RTRIM(LName1) + ', ' + RTRIM(FName1) as "Fixed-Length Name - Trimmed"
from #Names
Go
Drop Table #Names
go
David Gugick - SQL Server MVP
Quest Software

Concatenate list of values under a group

I have the following dataset:

State ZIP Homes Schools

WA 98007 2000 4

WA 98052 3000 5

WA 98079 2000 3

Now if I have set the group by expression on State but as display if I want to show it as “[98007, 98052, 98079]” how can I accomplish this.

My report needs to show:

State Homes Schools

[98007, 98052, 98079] 7000 12

Any help will be greatly appreciated

SPJ11, Why dont you do this in SQL itselves? You can also do this in SSRS. At the group level use,

Sum(Fields!Homes.value), Sum(Fields!Schools.value) -- these will retun the counts correctly.

I am not sure as how to concatnate the zip codes. I thought this should work

Join(Fields!Zip.Value) -- but this does not seem to work, I will see if I can come with something else...

|||

Never mind. I could make it work. Thanks to everyone who spent time on this thread

sql sql

Concatenate int & var char - SQL

Hi,

I am trying to write some simple SQL to join two fields within a table, the primary key is an int and the other field is a varchar.

But i am receiving the error:

'Conversion failed when converting the varchar value ',' to data type int.

The SQL I am trying to use is:

select game_no + ',' + team_name as match
from result

Thanks

As the error message says you can concatenate similar datatype values and use CONVERT or CAST functions otherwise to make them simialr.

selectConvert(Varchar,game_no)+','+ team_nameas match

from result

|||

Yep. Why the parser is so stupid that it assumes the presence of one number means all other exprssions must evaluate to a number, rather than the other way around, is a mystery to me.

Concatenate Input Columns

Hi,

In my data flow taks, The Source data is coming from AS400 has 4 columns,

I need to achieve the followings and require your help.

1. Generate a new column which will be combination of concating these 4 columns.

2. Need to add an extra row for Header & Footer.

Please Help.

Concatenation is achieved using the Derived Column component.

Adding your own header and footer rows is a bit more difficult because they need to have the same metadata as the data row. For this reason, concatenate all columns together so as to make a single, very wide, column. You can then use the UNION ALL component to put your header, data and footer together. The header and footer will probably be created using a source script component - though I'll leave that up to you.

-Jamie

|||

Re. Regarding Derived Column component

Source Columns

Col1 smallint, Col2 smallint, Col3 Decimal(12,4), Col4 Decimal(16,4)

Data will be the new derived column

Data = @.[User::TimeStamp] + "D" + Col1 + Col2 + Col3 + Col4

I need to know how to use Cast Operators as Data is DT_STR and Col1 & Col2 are smallint.

Thanks

|||

Look in the top right hand corner of the Derived Column UI. All the type cast operators are in there.

They are also all in BOL which you obviously haven't looked at:

http://msdn2.microsoft.com/en-us/library/ms141260.aspx

http://msdn2.microsoft.com/en-us/library/ms141704.aspx

BOL should ALWAYS be your first port of call. Not this forum!

-Jamie

|||

I have created two source script component one for Header and one for Footer. I am using OLE DB source for the detail records. While doing the union All, It just put the detail first and then Header and Footer.

Although Union All is setup like that

Union All Input 1 is Header

Union All Input 2 is detail and

Union All Input 3 is Footer

How can I make sure they go by order (Header,Detail,Footer).

Thanks again for your help

|||

Ahhh..I didn't think about that. Sorry. There is no way to guarantee the order.

I've thought of another way actually. Use a single script component transform to add the header and footer. It will have to be an asynchronous component.

Sorry for putting you on the wrong path.

-Jamie

Concatenate field based on unique id. (Follow up)

thanks for your earlier reply.

If i want to order the IDs by a Timestamp column in descending order how do i do it. I couldn't do it in Inner query.
right now it gives in random order. Is there any other way to get it?

select t3.id
, substring(
max(case t3.seq when 1 then ',' + t3.comment else '' end)
+ max(case t3.seq when 2 then ',' + t3.comment else '' end)
+ max(case t3.seq when 3 then ',' + t3.comment else '' end)
+ max(case t3.seq when 4 then ',' + t3.comment else '' end)
+ max(case t3.seq when 5 then ',' + t3.comment else '' end)
, 2, 8000) as comments
-- put as many MAX expressions as you expect items for each id
from (
select t1.id, t1.comment, createTS, count(*) as seq
from your_table as t1 join your_table as t2
on t2.id = t1.id and t2.comment <= t1.comment
group by t1.id, t1.comment, createTS
order by createTS desc -> gives error
) as t3
group by t3.id;

Got it working. I used 'top 100 percent" and used the ORDER BY in inner query.
Thanks.|||Note that using TOP 100 PERCENT is still not guaranteed to work. It depends on the query plan and even more so in SQL Server 2005. The use of ORDER BY clause is specific to a scope only and in your example to the derived table. Generally, you should not rely on the order in which the rows are processed in a SELECT statement. You should basically consider a SELECT statement source as an unordered set of rows. In your example, you can achieve the results by changing the condition:

t2.Comment <= t1.Comment

t2.CreateTs <= t1.CreateTs

This assumes that time stamp value is unique per id and this would guarantee that the sequence number is based on sorting the values in ascending order. You can incorporate additional conditions to handle matching time stamps and different comments. As I said before, doing these type of operations in SQL is not the right approach. These can be done very easily on the client side and with less work / assumptions.

Concatenate Dimension Attributes

Hi people,

This might be a really simple one hopefully, but is there a way I can create an attribute that is 2 other attributes concatenated, i.e.

Dim_Employee

EmployeeName

Employee Surname

Can I get EmployeeFullname by concatenating Name & Surname and if so how?

In SSAS2005 you create a new named calculation on the table in the datasource view.

Use TSQL EmployeeName + ',' + [Employee SureName]

In AS2000 you will have to write this TSQL in the name column for the dimension level. You do this in the dimension editor.

HTH

Thomas Ivarsson

|||

Cheers.

Easy when you know how :)

Concatenate Date & Time

I have 2 fields, dtDate and dtTime. dtDate is datetime and dtTime is
nvarchar(8) type. I have 2 problems.
1. I need a way to concatenate the 2 fields into 1 datetime field with a
select statement
2.In my example data below, you can see that dtTime is in a "military" time
format. Is there a way within SQL to convert it to a normal time format with
the AM/PM?
Any ideas or help would be greatly appreciated.
Example of Data ******************
dtDate dtTime
---
3/14/2006 12:00:00 AM 01:21:57
3/15/2006 12:00:00 AM 14:42:53Why are these separate? Anyway, try this, untested...
SELECT CONVERT(CHAR(10), dtDate, 120) + ' ' +
LTRIM(SUBSTRING(CONVERT(CHAR(22), dtTime, 22), 9, 14)) FROM tablename
Of course, this will only work if all of your dtTime values are valid times.
Since you chose NVARCHAR for some reason, this is an extra hassle to
validate / constrain.
"Scott Bailey" <sbailey@.mileslumber.com> wrote in message
news:%23y6Ns69YGHA.4580@.TK2MSFTNGP03.phx.gbl...
>I have 2 fields, dtDate and dtTime. dtDate is datetime and dtTime is
>nvarchar(8) type. I have 2 problems.
> 1. I need a way to concatenate the 2 fields into 1 datetime field with a
> select statement
> 2.In my example data below, you can see that dtTime is in a "military"
> time format. Is there a way within SQL to convert it to a normal time
> format with the AM/PM?
> Any ideas or help would be greatly appreciated.
>
> Example of Data ******************
> dtDate dtTime
> ---
> 3/14/2006 12:00:00 AM 01:21:57
> 3/15/2006 12:00:00 AM 14:42:53
>|||Your code returns just the date part like:
2006-03-14
Can you modify it to display the date and time? Also, can you have the whole
result converted to datetime format?
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23wruO%239YGHA.4688@.TK2MSFTNGP04.phx.gbl...
> Why are these separate? Anyway, try this, untested...
> SELECT CONVERT(CHAR(10), dtDate, 120) + ' ' +
> LTRIM(SUBSTRING(CONVERT(CHAR(22), dtTime, 22), 9, 14)) FROM tablename
> Of course, this will only work if all of your dtTime values are valid
> times. Since you chose NVARCHAR for some reason, this is an extra hassle
> to validate / constrain.
>
> "Scott Bailey" <sbailey@.mileslumber.com> wrote in message
> news:%23y6Ns69YGHA.4580@.TK2MSFTNGP03.phx.gbl...
>|||One last note, I didn't create this db, I just inherited it or I would never
have split the Date and Time into different fields.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23wruO%239YGHA.4688@.TK2MSFTNGP04.phx.gbl...
> Why are these separate? Anyway, try this, untested...
> SELECT CONVERT(CHAR(10), dtDate, 120) + ' ' +
> LTRIM(SUBSTRING(CONVERT(CHAR(22), dtTime, 22), 9, 14)) FROM tablename
> Of course, this will only work if all of your dtTime values are valid
> times. Since you chose NVARCHAR for some reason, this is an extra hassle
> to validate / constrain.
>
> "Scott Bailey" <sbailey@.mileslumber.com> wrote in message
> news:%23y6Ns69YGHA.4580@.TK2MSFTNGP03.phx.gbl...
>|||I was basing it on this:
SELECT CONVERT(CHAR(10), GETDATE(), 120) + ' ' +
LTRIM(SUBSTRING(CONVERT(CHAR(22), GETDATE(), 22), 9, 14));
Which returns:
2006-04-19 7:40:33 PM
Maybe it will work better like this:
SELECT CONVERT(CHAR(10), dtDate, 120) + ' ' +
LTRIM(SUBSTRING(CONVERT(CHAR(22), CONVERT(DATETIME, dtTime), 22), 9, 14));
I would put this into a view so you don't have to repeat this calculation
everywhere.
If that still doesn't yield the correct results, then please post DDL and
sample data so we can actually try and reproduce your issue.
"scott" <sbailey@.mileslumber.com> wrote in message
news:eHuNqXAZGHA.3880@.TK2MSFTNGP04.phx.gbl...
> Your code returns just the date part like:
> 2006-03-14
> Can you modify it to display the date and time? Also, can you have the
> whole result converted to datetime format?
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:%23wruO%239YGHA.4688@.TK2MSFTNGP04.phx.gbl...
>|||I was basing it on this:
SELECT CONVERT(CHAR(10), GETDATE(), 120) + ' ' +
LTRIM(SUBSTRING(CONVERT(CHAR(22), GETDATE(), 22), 9, 14));
Which returns:
2006-04-19 7:40:33 PM
Maybe it will work better like this:
SELECT CONVERT(CHAR(10), dtDate, 120) + ' ' +
LTRIM(SUBSTRING(CONVERT(CHAR(22), CONVERT(DATETIME, dtTime), 22), 9, 14));
I would put this into a view so you don't have to repeat this calculation
everywhere.
If that still doesn't yield the correct results, then please post DDL and
sample data so we can actually try and reproduce your issue.
"scott" <sbailey@.mileslumber.com> wrote in message
news:eHuNqXAZGHA.3880@.TK2MSFTNGP04.phx.gbl...
> Your code returns just the date part like:
> 2006-03-14
> Can you modify it to display the date and time? Also, can you have the
> whole result converted to datetime format?
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:%23wruO%239YGHA.4688@.TK2MSFTNGP04.phx.gbl...
>|||thank you. it works.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ug$RJsAZGHA.1228@.TK2MSFTNGP02.phx.gbl...
>I was basing it on this:
> SELECT CONVERT(CHAR(10), GETDATE(), 120) + ' ' +
> LTRIM(SUBSTRING(CONVERT(CHAR(22), GETDATE(), 22), 9, 14));
> Which returns:
> --
> 2006-04-19 7:40:33 PM
> Maybe it will work better like this:
> SELECT CONVERT(CHAR(10), dtDate, 120) + ' ' +
> LTRIM(SUBSTRING(CONVERT(CHAR(22), CONVERT(DATETIME, dtTime), 22), 9, 14));
> I would put this into a view so you don't have to repeat this calculation
> everywhere.
> If that still doesn't yield the correct results, then please post DDL and
> sample data so we can actually try and reproduce your issue.
>
> "scott" <sbailey@.mileslumber.com> wrote in message
> news:eHuNqXAZGHA.3880@.TK2MSFTNGP04.phx.gbl...
>

Thursday, March 29, 2012

Concatenate strings in group column

Concatenate strings from different rows

Concatenate strings after assigning text in place of bit strings

Concatenate String and Pass to FORMSOF?

Concatenate String and Pass to FORMSOF?

Concatenate String and Pass to FORMSOF?

Concatenate string

Concatenate single quote...

Concatenate Rows into Columns

Concatenate Rows into Columns

Concatenate Rows

Concatenate Rows

Concatenate Query Results into a String most effectively

Concatenate problem.

Concatenate problem.

concatenate problem

concatenate nulls in SQL server

Concatenate nuimbers

Concatenate nuimbers

Concatenate Multiple Rows?

Concatenate multiple reports into one pdf

Concatenate Multiple Records Into One Field

concatenate multiple fields

Concatenate Lname and Fname Columns

Concatenate list of values under a group

Concatenate int & var char - SQL

Concatenate Input Columns

Concatenate field based on unique id. (Follow up)

Concatenate Dimension Attributes

Concatenate Date & Time

Sql Comparing two tables

Blog Archive

About Me