SQL Server Performance

Concatenation of Records in a Table

Discussion in 'SQL Server 2005 Integration Services' started by pwilson, Jun 25, 2009.

  1. pwilson New Member

    I have a table (for example) that looks like this...
    ORDER# ITEM COLOR
    1 Bike RED
    1 Bike BLUE
    1 Bike GREEN
    1 Bike White
    Through SSIS i would like to take the above table and produce results as follows....
    ORDER ITEM COLOR
    1 Bike RED, BLUE,GREEN, WHITE
    Can anyone point me into the right direction?
    pwilson, Jun 25, 2009
    #2

  2. FrankKalis Moderator

    Welcome to the forum!
    This might get you going:
    DECLARE @table TABLE (Order# int, item varchar(10), color varchar(10))
    INSERT INTO @table
    SELECT 1, 'Bike', 'RED' UNION ALL
    SELECT 1, 'Bike', 'BLUE' UNION ALL
    SELECT 1, 'Bike', 'GREEN' UNION ALL
    SELECT 1, 'Bike', 'WHITE'


    SELECT
    T1.Order#,
    T1.item,
    MIN (SUBSTRING (MyList, 2, LEN (MyList)))
    FROM
    @table T1
    CROSS APPLY
    (SELECT
    ',' + T2.color
    FROM
    @table T2
    WHERE
    T1.Order# = T2.Order# AND
    T1.Item = T2.item
    FOR XML PATH ('')) L (MyList)
    GROUP BY
    T1.Order#, T1.item
    FrankKalis, Jun 26, 2009
    #3

  3. pwilson New Member

    Thanks for the welcome and the help. Your example worked like a charm.
    pwilson, Jun 29, 2009
    #4

  4. Madhivanan Moderator

    Also refer
    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254
    Madhivanan, Jun 30, 2009
    #5

Share This Page