Concatenating strings....... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Concatenating strings.......

Author

Topic

nikolasapl
Starting Member

4 Posts

Posted - 2008-11-22 : 09:30:49

How can I convert a table of the form:

field1 | field2
1 | A
1 | B
1 | C
2 | A
2 | G
3 | C

into a table of the following form:

field1 | field2
1 | A,B,C
2 | A,G
3 | C

That is, concatenate the strings in field2 that have the same value in field1.

Also, is it possible to do that WITHOUT using a user-defined function?

Many thanks in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-22 : 11:18:18

create a UDF like this


CREATE FUNCTION ConcatValues
(
@field1 int
)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @List varchar(8000)

SELECT @List=COALESCE(@List) + field2
FROM YourTable
WHERE field1=@field1

RETURN @List
END

then use it like below


SELECT DISTINCT field1,dbo.ConcatValues(field1)
FROM YourTable

nikolasapl
Starting Member

4 Posts

Posted - 2008-11-22 : 13:44:52

Is there another way to do the following without using a function?

Don't have write access to the database...

quote:

Originally posted by visakh16

create a UDF like this


CREATE FUNCTION ConcatValues
(
@field1 int
)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @List varchar(8000)

SELECT @List=COALESCE(@List) + field2
FROM YourTable
WHERE field1=@field1

RETURN @List
END

then use it like below


SELECT DISTINCT field1,dbo.ConcatValues(field1)
FROM YourTable

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-22 : 13:58:31

yup. create a temporary table first


CREATE TABLE #Temp
(
ID int identity(1,1),
field1 int,
field2 varchar(8000)
)

INSERT INTO #Temp (field1)
SELECT DISTINCT field1
FROM YourTable

DECLARE @ID int,@List varchar(1000),@field1 int

SELECT @ID=MIN(ID)
FROM field1

WHILE @ID IS NOT NULL
BEGIN

SELECT @List='',@field1=field1
FROM #Temp
WHERE ID=@ID

SELECT @List=@List + field2 + ','
FROM YourTable
WHERE field1=@field1

UPDATE #Temp
SET field2=@List
WHERE field1=@field1

SELECT @ID=MIN(ID)
FROM #Temp
WHERE ID >@ID
END

SELECT field1,field2 FROM #Temp

nikolasapl
Starting Member

4 Posts

Posted - 2008-11-23 : 12:23:41

I came up with a similar solution, but I use a while loop with a cursor to process each line of my table.

But I know that cursors are not efficient at all, especially when they are executed in the same batch with other queries (the analyzer goes crazy in that case and takes a LONG time to execute the cursor loop).

So which solution do you think is the most efficient in terms of execution time? I really need the most efficient one.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-23 : 12:31:34

check it out both and compare their execution times.I dont have a sql box now to test.

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2008-11-25 : 01:58:23

Try this.....
declare @t table(col1 int , col2 varchar(5))
insert @t
select 1 , 'A' union all
select 1 , 'B' union all
select 1 , 'C' union all
select 2 , 'A' union all
select 2 , 'G' union all
select 3 , 'C'

select col1, left(CSP_List,len(CSP_List)-1)
from @t t
cross apply
(select col2 + ',' from @t
where col1 = t.col1
for xml path('')
) as c(CSP_List)
group by
col1, csp_list

--------------------------------------------------
S.Ahamed

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-25 : 02:11:21

quote:
Originally posted by pbguy

Try this.....
declare @t table(col1 int , col2 varchar(5))
insert @t
select 1 , 'A' union all
select 1 , 'B' union all
select 1 , 'C' union all
select 2 , 'A' union all
select 2 , 'G' union all
select 3 , 'C'

select col1, left(CSP_List,len(CSP_List)-1)
from @t t
cross apply
(select col2 + ',' from @t
where col1 = t.col1
for xml path('')
) as c(CSP_List)
group by
col1, csp_list

--------------------------------------------------
S.Ahamed

this wont work in sql 2000
apply operator is only available from sql 2005 and above
i dont think OP is using sql 2005 as this is posted in sql 2000 forum

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2008-11-25 : 03:33:28

oh...i am sorry...for 2000
declare @t table(col1 int , col2 varchar(5))
insert @t
select 1 , 'A' union all
select 1 , 'B' union all
select 1 , 'C' union all
select 2 , 'A' union all
select 2 , 'G' union all
select 3 , 'C'

declare @str as varchar(100)

--2000

select distinct col1,
replace((select col2 + ',' from @t where col1 = t.col1 for xml path('')) + '@$%#',',@$%#','') as List
from @t t

--------------------------------------------------
S.Ahamed

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-25 : 03:36:37

quote:
Originally posted by pbguy

oh...i am sorry...for 2000
declare @t table(col1 int , col2 varchar(5))
insert @t
select 1 , 'A' union all
select 1 , 'B' union all
select 1 , 'C' union all
select 2 , 'A' union all
select 2 , 'G' union all
select 3 , 'C'

declare @str as varchar(100)

--2000

select distinct col1,
replace((select col2 + ',' from @t where col1 = t.col1 for xml path('')) + '@$%#',',@$%#','') as List
from @t t

--------------------------------------------------
S.Ahamed

have you tested this in 2000? FOR XML PATH is also available only from sql 2005 onwards.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources