Home Tsql transpose rows to columns, grouping by a column
Reply: 1

Tsql transpose rows to columns, grouping by a column

ʞᴉɯ
1#
ʞᴉɯ Published in 2017-12-07 15:46:40Z

I've this set of data:

cId    item     value
-------------------------------------
1      A        string1
2      A        string2
3      A        string3
1      B        string4
2      B        string5
1      C        string6
3      C        string7

And i would to transpose item ad cId rows into columns, in order to have this result:

cId        A           B             C
----------------------------------------------
1          string1     string4       string6  
2          string2     string5       NULL
3          string3     NULL          string7

item and cId rows are not fixed, so i think i need some of dynamic sql for pivoting. How can i do in tsql? Thanks

Chris Mack
2#
Chris Mack Reply to 2017-12-07 16:06:37Z

The following will do it:

DECLARE @pivotFields varchar(255) =
    STUFF(
        (
            SELECT ', [' + item + ']'
            FROM
                (
                    SELECT DISTINCT item
                    FROM YourTable
                ) Q
            ORDER BY item
            FOR XML PATH ('')
        )
        , 1, 2, ''
    )

DECLARE @sql varchar(1000) =
    '
        SELECT cId, ' + @pivotFields + '
        FROM
            (
                SELECT
                    cId
                    , item
                    , value
                FROM YourTable
            ) Q
            PIVOT
            (
                MAX(value)
                FOR item IN (' + @pivotFields + ')
            ) P
    '

EXEC (@sql)
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.307457 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO