PIVOT a table with JSON in SQL Server(2017 or 2019)


  

 
---SQL Server 2017 or 2019
CREATE TABLE mytable(A varchar(10) NOT NULL, B varchar(10) NOT NULL)
INSERT mytable VALUES
(10,  -1),(50,  10),(51,  10),(52,  10),
(11,  -1),(60,  11),(61,  11),(62,  11),(63,  11),
(12,  -1),(70,  12),(71,  12),(72,  12),(73,  12),(74,  12)

;with mycte as (
SELECT (
SELECT B, Vals = JSON_QUERY('["' + STRING_AGG(STRING_ESCAPE(A, 'json'), '","') + '"]')
    FOR JSON PATH
) jsCol
FROM mytable 
where B-1
GROUP BY B
)

Select 
/*jsCol, */
JSON_VALUE(jsCol,'$[0].B') MN
,JSON_VALUE(jsCol,'$[0].Vals[0]') as S1
,JSON_VALUE(jsCol,'$[0].Vals[1]') as S2
,JSON_VALUE(jsCol,'$[0].Vals[2]') as S3
,JSON_VALUE(jsCol,'$[0].Vals[3]') as S4
,JSON_VALUE(jsCol,'$[0].Vals[4]') as S5
,JSON_VALUE(jsCol,'$[0].Vals[5]') as S6
from mycte
 
 
	 
drop TABLE mytable

  

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/d42591a9-2b0b-4943-8fe2-a6bb9fc08c5e/query-to-produce-a-table-with-7-columns-based-on-the-values-in-a-two-column-table?forum=transactsql

An Easier Way of Pivoting Data in SQL Server

https://stackoverflow.com/questions/49574006/sql-server-query-columns-to-json-object-with-group-by



Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s