Post reply

Create CSV list from rows, SQL 2000

  • 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!

  • January 27, 2011 at 12:48 pm

    #1279324

    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!

  • January 27, 2011 at 1:00 pm

    #1279333

    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

  • January 28, 2011 at 11:02 am

    #1279839

    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/

  • January 28, 2011 at 1:32 pm

    #1279935

    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

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

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