Post reply

Convert rows to one column

  • November 30, 2010 at 10:11 am

    #243404

    I need to be able to do the following:

    ID,VALUE

    1,A1

    1,A2

    1,A3

    2,B1

    3,C1

    3,C2

    Convert the above to:

    ID,VALUE

    1,A1|A2|A3

    2,B1

    3,C1|C2

    Basically I need to be able to convert the rows to one column. I've tried a few things but not having much luck.

  • November 30, 2010 at 10:49 am

    #1256908

    Search online for "string concatenate for xml". You'll find good solutions with that.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • November 30, 2010 at 12:25 pm

    #1256963

    Try this:

    -- See how this starts off with a table and data in it?

    -- If you had provided us the data in this format,

    -- it would have made things easier for all of the

    -- volunteers on this site to help you out.

    DECLARE @test-2 TABLE (ID int, Value char(3));

    INSERT INTO @test-2 (ID,VALUE)

    SELECT 1, 'A1' UNION ALL

    SELECT 1, 'A2' UNION ALL

    SELECT 1, 'A3' UNION ALL

    SELECT 2, 'B1' UNION ALL

    SELECT 3, 'C1' UNION ALL

    SELECT 3, 'C2';

    WITH CTE AS

    (

    -- get the unique ID

    SELECT ID

    FROM @test-2

    GROUP BY ID

    )

    -- for each ID value returned in the CTE

    -- use a correlated subquery to get a

    -- pipe-delimited string of values for that ID

    SELECT CTE.ID,

    VALUE = STUFF((SELECT '|' + VALUE

    FROM @test-2 t

    WHERE t.ID = CTE.ID

    FOR XML PATH(''),TYPE).value('.','varchar(8000)'),1,1,'')

    FROM CTE

    ORDER BY ID;

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • November 30, 2010 at 1:51 pm

    #1257025

    Thanks that worked great. Appreciate the tip on how to post. Will do that going forward.

  • December 2, 2010 at 7:08 pm

    #1258182

    riyaz.mohammed (11/30/2010)

    Thanks that worked great. Appreciate the tip on how to post. Will do that going forward.

    Great!

    Yep, posting actual code that folks can cut-and-paste into SSMS greatly increases the number of folks that will even look at your problem. You might want to look at the first link in my signature for recommended ways to post data - just be sure to include what the expected results should be based upon the sample data posted.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

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

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