SQL Server Performance

Get as Comma seperated values

Discussion in 'SQL Server 2008 General Developer Questions' started by i.netdeveloper, Nov 5, 2009.

  1. i.netdeveloper Member

    I have few tables from which I have to extract the data in such a way that it comes out as comma seperated values.
    For example I extracted the data in temp table like this
    #Temp
    empid emprole empprod empccrole
    1 null p1 null
    1 null p2 null
    1 r1 null null
    1 r2 null null
    1 null null c1
    2 rr1 null null
    2 rr2 null null
    and so on
    and the output should look like
    empno emprole empprod empcc
    1 r1,r2 p1,p2 c1
    2 rr1,rr2 null null
    Can any one help me with this I tired FOR
    XML PATH('') but did't help
    Thanks
    i.netdeveloper, Nov 5, 2009
    #2

  2. FrankKalis Moderator

    See if this helps:
    CREATE TABLE #Temp
    (
    empid int,
    emprole varchar(5),
    empprod varchar(2),
    empccrole varchar(2)
    )

    INSERT INTO #Temp
    (empid, emprole, empprod, empccrole)
    SELECT 1, null, 'p1', NULL
    UNION ALL
    SELECT 1, NULL, 'p2', NULL
    UNION ALL
    SELECT 1, 'r1', NULL, NULL
    UNION ALL
    SELECT 1, 'r2', NULL, NULL
    UNION ALL
    SELECT 1, NULL, NULL, 'c1'
    UNION ALL
    SELECT 2, 'rr1', NULL, NULL
    UNION ALL
    SELECT 2, 'rr2', NULL, NULL


    SELECT
    t1.empid,
    NULLIF(MAX(SUBSTRING(x.emprole, 2, LEN(x.emprole))), '') emprole,
    NULLIF(MAX(SUBSTRING(y.empprod, 2, LEN(y.empprod))), '') empprod,
    MAX(t1.empccrole)
    FROM
    #Temp t1
    CROSS APPLY
    (SELECT
    ',' + t2.emprole
    FROM
    #Temp t2
    WHERE
    t1.empid = t2.empid
    ORDER BY
    t2.emprole
    FOR XML PATH('')) AS x(emprole)
    CROSS APPLY
    (SELECT
    ',' + t2.empprod
    FROM
    #Temp t2
    WHERE
    t1.empid = t2.empid
    ORDER BY
    t2.emprole
    FOR XML PATH('')) AS y(empprod)
    GROUP BY
    t1.empid;

    DROP TABLE #Temp
    FrankKalis, Nov 5, 2009
    #3

Share This Page