SQL Pivot after Cross Apply

Question

I am trying to do a pivot in SQL Server wherein I need to pivot the following table:

key       value
column1    billy
column2    billy@billy.com
column5    NULL
column6    false
column9    true

I want the values in the "key" column to be the column headers. Another problem here is that I am using a CROSS APPLY to generate this table from another table. I start with:

select * from jtable cross apply openjson(json)

which results in the table format shown above.

If I don't use the cross apply, I get a table that looks like this:

ID json
1  "column1":billy, "column2":billy@billy.com
2  "column1":steve, "column2":steve@etc.com

So I'm trying to end up with a table that looks like this:

column1    column2            column3
billy      billy@billy.com    false
steve      steve@etc.com      false

score 1 · Accepted Answer · answered Nov 30 '16 at 06:37

Something like this:

SELECT *
FROM
(
    select ROW_NUMBER() OVER (ORDER BY(SELECT 1)) AS ID
          ,* 
    from jtable 
    cross apply openjson(json)
) DS
PIVOT
(
    MAX([value]) FOR [key] IN ([column1], [column2], [column3], ... , [columnN] )
) PVT

If you do not know the number of the columns, search for dynamic PIVOT. If you need to sort by other criteria (generate the ID in other special way) - you can change the ROW_NUMBER function.

SQL Pivot after Cross Apply

1 Answers1