CODEWITHSUNDEEP
sqlsql-servert-sqlpivotcross-apply
SUMguy
SUMguy

Reputation: 1673

SQL Pivot after Cross Apply

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

key       value
column1    billy
column2    [email protected]
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":[email protected]
2  "column1":steve, "column2":[email protected]

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

column1    column2            column3
billy      [email protected]    false
steve      [email protected]      false

Upvotes: 2

Views: 2325

Answers (1)

gotqn
gotqn

Reputation: 43636

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.

Upvotes: 1

Related Questions