Create CSV list from rows, SQL 2000

Question

Post reply

Create CSV list from rows, SQL 2000

touchmeknot

SSCommitted

Points: 1769
More actions
January 27, 2011 at 10:48 am

#233560

Hello,
I am creating a csv list in SQL 2000, this is the key here. I can't use for xml path :(.
Say the values are
Id Value
1 A
1 B
1 C
2 A
2 B
I need output as
1 A,B,C
2 A,B
Thanks in advance!

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323546 More actions · Answer 1

here's a complete example you can use as a model:

-==snip oops it had a CTE in it; rewriting it... hang on

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

drew.allen SSC Guru Points: 77014 More actions · Answer 2

You know that there is a separate forum for SQL 2000/7.0.

You should read Jeff's article about the "quirky" update. http://www.sqlservercentral.com/articles/68467/. There are a lot of caveats for "quirky" updates, and Jeff goes into them in his article.

You just need to replace the addition operator with a concatenation. So the code will be something like

SET @List = List = CASE WHEN ID = @Old_ID THEN @List + ', ' + Value Else Value END

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Mike01 SSChampion Points: 11420 More actions · Answer 3

I don't have a 2000 test environment, but you can try this using XML

create table #T (

ID int ,

Val varchar(100));

insert into #T (ID, Val)

select 1 ,'A' union all

select 1 ,'B' union all

select 1 ,'C' union all

select 2 ,'A' union all

select 2 ,'B'

select distinct

ID, stuff( -- Gets rid of leading comma

(select ', ' + Val -- No column name gets rid of that part in the XML

from #T t2

where t1.ID = t2.ID

for xml

path(''), -- ('') gets rid of row tags

TYPE).value('.[1]', 'varchar(MAX)') -- value query allows XML characters

,1,2,'') -- part of Stuff statement

from

#T t1;

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

drew.allen SSC Guru Points: 77014 More actions · Answer 4

FOR XML PATH was introduced in SQL 2005 and varchar(max) was also introduced in SQL 2005. The OP already told you that he couldn't use FOR XML PATH.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA