CODEWITHSUNDEEP
sql-serverdynamicpivot
user1859252
user1859252

Reputation: 21

SQL Server dynamic groupby pivot

I have the following structure in a SQL Server 2008 database. I am trying to create a script to replicate the output shown below.

It is generated by: 

groupby on RPT_ID, FILE_ID, LINE_ID,

distinct columns from COLUMN_LITH and 'first' values in COLUMN_VALUES (COLUMN_NAME is dropped)

I am a beginner in SQL so this is beyond me. Any help whatsoever would be greatly appreciated.

Thanks in advance

SOURCE:

enter image description here

TARGET:

enter image description here

Upvotes: 0

Views: 47

Answers (1)

Lonely Planeteer
Lonely Planeteer

Reputation: 389

Using dynamic pivot, it might be an option.

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.column_name) 
            FROM your_table_name c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT RPT_ID, FILE_ID, LINE_ID' + @cols + ' from 
            (
                select RPT_ID
                    , FILE_ID
                    , LINE_ID
                    , column_values
                    , column_name
                from your_table_name
           ) x
            pivot 
            (
                 max(column_values)
                for column_name in (' + @cols + ')
            ) p '


execute(@query)

Upvotes: 1

Related Questions