Post reply

Need to group and rearrange the data (pivot the data concatenate it)

  • December 17, 2011 at 6:20 pm

    #391152

    Hello.

    Can someone please show me an example of how I can accomplish this in SQL server 2008:

    My simple query:

    SELECT Name, Color

    from TableName

    Results:

    But I need the output to be like this:

    I greatly appreciate any help. Thanks!

  • December 18, 2011 at 2:06 am

    #1422887

    here is an example using 'FOR XML PATH'

    -- create some data

    with produce (id,fruit, varieties)

    as (

    SELECT 101,'Apple', '3' UNION ALL

    SELECT 101,'Banana', '2' UNION ALL

    SELECT 102,'Orange', '1' UNION ALL

    SELECT 103,'Melon' ,'2' UNION ALL

    SELECT 103,'Grape' ,'1' UNION ALL

    SELECT 104,'Apple' ,'1' UNION ALL

    SELECT 105,'Banana' ,'1' UNION ALL

    SELECT 105,'Kiwi' ,'1' UNION ALL

    SELECT 105,'Tangerine' ,'1' UNION ALL

    SELECT 106,'Mango' ,'3' UNION ALL

    SELECT 106,'Melon' ,'2'

    )

    --query as follows

    SELECT id,

    Stuff((SELECT ',' + fruit

    FROM produce p2

    WHERE p1.id = p2.id

    ORDER BY p2.fruit --- sort by Fruit name

    FOR XML PATH('')), 1, 1, ' ')

    FROM produce p1

    GROUP BY id

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

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

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