SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Converting Multiple Rows into a CSV String
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 08/02/2000 :  03:19:04  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
Joern asks How do I aggregate data from multiple rows into a delimited list?

Article Link.

Anonymous
Starting Member

0 Posts

Posted - 01/25/2001 :  00:25:42  Show Profile  Reply with Quote
Another method for generating a CSV

The below is something I had hacked together before reading the article here -- it's simpler to understand than using cursors, although the performance is not wonderful if you're generating a large list. I'll present the procedure in a pretty vanilla form; obviously, you'd want to customize the data types and lengths to suit your own purposes. I think it's pretty self-explanatory, but feel free to post if you have any questions or comments about it.

matt


CREATE proc [dbo].[sp_util_buildstring]
(
@sqlquery varchar(1000),
@delimiter varchar(1)
)
as

declare @liststring varchar(100)
select @liststring = ''

create table #templist (
temp_id int identity,
tempval varchar(100)
)

insert into #templist (tempval) exec(@sqlquery)

while exists (select temp_id from #templist)
begin
select @liststring = @liststring + @delimiter + (select tempval from #templist where temp_id = (select min(temp_id) from #templist))
delete from #templist where temp_id = (select min(temp_id) from #templist)
end

select @liststring = right(@liststring, datalength(@liststring) - datalength(@delimiter))
select @liststring

GO

Go to Top of Page

Juls
Yak Posting Veteran

USA
51 Posts

Posted - 06/05/2002 :  11:35:31  Show Profile  Reply with Quote
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

Go to Top of Page

robvolk
Most Valuable Yak

USA
15659 Posts

Posted - 06/05/2002 :  11:47:35  Show Profile  Visit robvolk's Homepage  Reply with Quote
Uhhhhhhhhhhhhhhhh, what's the problem exactly? Can you provide some sample data, and the output you want?

Go to Top of Page

Juls
Yak Posting Veteran

USA
51 Posts

Posted - 06/05/2002 :  12:29:18  Show Profile  Reply with Quote
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

Go to Top of Page

robvolk
Most Valuable Yak

USA
15659 Posts

Posted - 06/05/2002 :  12:44:48  Show Profile  Visit robvolk's Homepage  Reply with Quote
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.

Go to Top of Page

Page47
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 06/05/2002 :  12:47:29  Show Profile  Reply with Quote
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>
Go to Top of Page

julesr
Starting Member

United Kingdom
13 Posts

Posted - 06/20/2002 :  07:41:49  Show Profile  Visit julesr's Homepage  Send julesr an ICQ Message  Reply with Quote
[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
Go to Top of Page

archngl
Starting Member

USA
1 Posts

Posted - 09/10/2002 :  12:16:43  Show Profile  Send archngl a Yahoo! Message  Reply with Quote
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
Go to Top of Page

sheehek
Starting Member

1 Posts

Posted - 10/11/2002 :  15:23:22  Show Profile  Reply with Quote
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







Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000