Article: Converting Multiple Rows into a CSV String

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

Site Related Forums

Article Discussion

Article: Converting Multiple Rows into a CSV String

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2000-08-02 : 03:19:04

Joern asks How do I aggregate data from multiple rows into a delimited list?

Article Link.

Juls
Yak Posting Veteran

51 Posts

Posted - 2002-06-05 : 11:35:31

quote:

Joern asks How do I aggregate data from multiple rows into a delimited list?<P>Article <a href="/item.asp?ItemID=256">Link</a>.

Hi, I tried using the code in the article do just what the article asks, however i can not get it exactly. I get all my values in one row separated by commas. However, I can't separate the values out with the identifier. Which ones go with which number.. 1, 2, 3

Please help

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-06-05 : 11:47:35

Uhhhhhhhhhhhhhhhh, what's the problem exactly? Can you provide some sample data, and the output you want?

Juls
Yak Posting Veteran

51 Posts

Posted - 2002-06-05 : 12:29:18

quote:

Uhhhhhhhhhhhhhhhh, what's the problem exactly? Can you provide some sample data, and the output you want?

Here it is:
I have a table that has values structured as follows:
PersonID Degree
55 MD
55 Phd
55 RN
60 MD
60 Phd

I need a stored procedure that will give me output like this:
PersonID Degree
55 MD, Phd, RN
60 MD, Phd

With the code above, as well as with the article code I only get the list of degrees in one line separated by commas, even though I pull out the Person ID it doesn't group by this ID

Thanks,
juls

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-06-05 : 12:44:48

Take a look here:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=15651

There are several other links within that one, you may have to drill down a bit, but there are at least 3 methods for doing this. Before you ask, it CANNOT be done with a single SELECT statement, but that's not an issue really.

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-06-05 : 12:47:29

Juls, I don't know why you can't get it to work . . .

This should...


/*
--This is the part you should always post so that ppl who
--come along to help you for free don't have to waste their
--time typing it in

create table Juls (
    PersonID int,
    Degree varchar(3) )

insert juls
select 55,'md'
union select 55,'phd'
union select 55,'rn'
union select 60,'md'
union select 60,'phd'
*/

declare @List varchar(100), @LastID int
select @List = '', @LastID = ''

select
    PersonID,
    Degree,
    convert(varchar(100),NULL) as list
into
    #rowset
from
    Juls
order by
    PersonID,
    Degree

update
    #rowset
set
    @List = list = case
                       when @LastID <> PersonID then Degree
                       else @List + ',' + Degree
                   end,
    @LastID = PersonID

select
    PersonID,
    max(list)
from
    #rowset
group by
    PersonID
go
drop table #rowset

<O>

julesr
Starting Member

14 Posts

Posted - 2002-06-20 : 07:41:49

[quote]
Juls, I don't know why you can't get it to work . . .

This should...

[code]

Thanks for posting this solution, it works great for me. I'd now like to take it one step further. My situation is virtually identical aside from the fact that Degree is an ntext field. I'd appreciate any insight you might have

Jules
http://www.charon.co.uk

archngl
Starting Member

1 Post

Posted - 2002-09-10 : 12:16:43

RocketScientist's solution can be achieved without the need for a cursor or a temp table with some fairly simple code with I think has been discussed here before.

Create table #Foo(col1 int, col2 varchar(10))

insert into #foo
Select 1, 'a'
union Select 2, 'b'
union Select 2, 'c'
union Select 2, 'd'
union Select 3, 'a'

Declare @string varchar(8000)
Set @String = ''
--Build the string list
Select @string = @string + cast(col2 as varchar) + ',' from #Foo where col1 = 2
--Trim the trailing comma
Select @String = left(@string,len(@string)-1)
--Here is your list for a single col1 value.
print @string

Now to solve your specific solution you can do 1 of 2 ways. with an update statement(see Page47 solution) to the output table or a cursor to cycle through the input statements. I'll use the cursor method below.

/*
Create table #Foo(col1 int, col2 varchar(10))

insert into #foo
Select 1, 'a'
union Select 2, 'b'
union Select 2, 'c'
union Select 2, 'd'
union Select 3, 'a'

Create table #Foo2(col1 int, col2 varchar(10))
*/

truncate table #foo2

Declare @col1 int
Declare @string varchar(8000)
declare Col1 insensitive scroll cursor
for select Distinct col1 from #foo
for read only

Open Col1

fetch next from Col1 into @col1

While @@Fetch_Status = 0
Begin
Set @String = ''
--Build the string list
Select @string = @string + cast(col2 as varchar) + ',' from #Foo where col1 = @col1
--Trim the trailing comma
Select @String = left(@string,len(@string)-1)
--Here is your list
Insert into #foo2 Select @Col1, @String
fetch next from Col1 into @col1
End

close Col1
Deallocate Col1

Select * from #foo2

Edited by - archngl on 09/10/2002 12:17:29

sheehek
Starting Member

1 Post

Posted - 2002-10-11 : 15:23:22

Here's how I would do it (may already be out there in another thread):
Step 1: table called tester and put in the data.
Step 2: create a user-defined function that uses COALESCE.
Step 3: create a single select query

The results:
1, a
2, b, c, d
3, a
4

Note the results even account for the scenerio where you just want the id and there are no letters/suffixes after it.

K.
------------------------------------------
--Step 1: table called tester and put in the data.
create table tester (MyId int null, MyLetter char(1) null)

insert into tester
select
1, 'a'
insert into tester
select
2 ,'b'
insert into tester
select
2 ,'c'
insert into tester
select
2 ,'d'
insert into tester
select
3, 'a'

insert into tester
select
4, null

--Step 2: create a user-defined function that uses COALESCE.
create function CreateCSVString(@nMyID int)
returns varchar(1000)
AS
BEGIN
DECLARE @sMyString varchar(1000)

select @sMyString = COALESCE(@sMyString + ', ', '') + MyLetter from tester
where myid = @nMyId

RETURN @sMyString
END

--Step 3: create a single select query

select cast(myID as varchar) + ISNULL(NULLIF(', ' + ISNULL(dbo.CreateCSVString(myID),''), ', '),'')
FROM tester t1
group by myID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources