Created
July 5, 2020 20:13
-
-
Save jingyang-li/2565fca2cd67e4e4d6d8357fb97e1d42 to your computer and use it in GitHub Desktop.
T-SQL Unpivot with JSON
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE TABLE #tmpTable ( | |
Friday varchar(50), | |
Monday varchar(50), | |
Saturday varchar(50), | |
Sunday varchar(50), | |
Thursday varchar(50), | |
Tuesday varchar(50), | |
Wednesday varchar(50), | |
fk_incomes int, | |
fk_life_stages int, | |
fk_met_areas int, | |
fk_neighbourhoods int, | |
local_date date) | |
INSERT #tmpTable VALUES | |
('[73,40,66,68,48,67]','[63,57,52,117,27,35]','[50,54,48,84,30,44]','[40,179,62,49,71,25]','[156,241,91,63,21,18]','[33,57,58,48,64,69]','[51,103,113,72,82,66]',3,3,0,6719,CONVERT(DATETIME, '2020-04-01', 120)) | |
,('[0,0,0,0,0,0]','[0,0,2,0,0,0]','[0,0,0,0,0,0]','[0,0,0,2,0,0]','[0,0,0,0,0,0]','[0,0,0,0,0,0]','[14,0,0,0,0,0]',4,2,0,25160,CONVERT(DATETIME, '2020-04-01', 120)) | |
,('[0,0,0,2,0,2]','[0,0,1,0,0,0]','[0,4,4,4,0,1]','[0,0,112,2,0,0]','[0,0,0,1,1,0]','[0,1,2,0,0,0]','[0,0,1,0,0,0]',5,2,0,26254,CONVERT(DATETIME, '2020-04-01', 120)), | |
('[0,2,18,77,103,0]','[10,47,47,99,4,0]','[3,7,32,29,42,2]','[0,3,0,5,0,1]','[2,55,51,16,42,0]','[3,0,32,36,52,32]','[2,41,68,61,3,0]',1,3,0,193189,CONVERT(DATETIME, '2020-04-01', 120)), | |
('[2,11,0,12,11,0]','[1,4,0,1,2,0]','[0,23,9,4,9,4]','[26,14,7,15,0,0]','[5,4,13,17,1,5]','[12,45,0,7,0,12]','[13,6,38,4,3,7]',3,3,0,193526,CONVERT(DATETIME, '2020-04-01', 120)), | |
('[24,23,48,51,31,11]','[25,32,19,23,11,11]','[9,31,76,73,32,11]','[8,15,30,29,14,12]','[27,42,114,50,13,25]','[43,35,8,215,11,10]','[48,127,43,73,23,32]',2,3,0,193640,CONVERT(DATETIME, '2020-04-01', 120)), | |
('[89,139,227,169,169,42]','[56,114,99,94,59,55]','[97,102,215,120,50,120]','[65,104,71,54,107,41]','[157,102,142,188,94,158]','[55,128,119,229,147,43]','[158,163,216,155,199,85]',1,2,0,194379,CONVERT(DATETIME, '2020-04-01', 120)) | |
;with mysource as ( | |
SELECT fk_neighbourhoods,local_date, | |
Friday , | |
Monday , | |
Saturday , | |
Sunday , | |
Thursday , | |
Tuesday , | |
Wednesday | |
, row_number() OVER (ORDER BY fk_neighbourhoods,local_date ) rn | |
FROM #tmpTable | |
) | |
,mycte (TheKey, TheValue) as ( | |
SELECT [KEY], Value from OpenJson(( | |
select * from mysource FOR JSON AUTO )) | |
WHERE type = 5) | |
,mycteFriday as ( | |
SELECT | |
JSON_VALUE(src.TheValue,'$.fk_neighbourhoods') fk_neighbourhoods, | |
JSON_VALUE(src.TheValue,'$.local_date') local_date | |
-- JSON_VALUE(src.TheValue,'$.Friday') Friday | |
--,JSON_VALUE(src.TheValue,'$.Monday') Monday | |
--,JSON_VALUE(src.TheValue,'$.Saturday') Saturday | |
--... | |
,unpvt.Value grp | |
,unpvt1.[key], unpvt1.[value] Friday | |
FROM mycte AS src | |
CROSS APPLY OpenJson(src.TheValue) AS unpvt | |
cross apply OpenJson(JSON_VALUE(src.TheValue,'$.Friday')) AS unpvt1 | |
WHERE unpvt.Type !=5 and | |
unpvt.[Key] not in ( | |
'Friday' | |
,'Monday' | |
,'Saturday' | |
,'Sunday' | |
,'Thursday' | |
,'Tuesday' | |
,'Wednesday' | |
,'fk_neighbourhoods' | |
,'local_date') | |
) | |
,mycteMonday as ( | |
SELECT | |
unpvt.Value grp | |
,unpvt1.[key], unpvt1.[value] as Monday | |
FROM mycte AS src | |
CROSS APPLY OpenJson(src.TheValue) AS unpvt | |
cross apply OpenJson(JSON_VALUE(src.TheValue,'$.Monday')) AS unpvt1 | |
WHERE unpvt.Type !=5 and | |
unpvt.[Key] not in ( | |
'Friday' | |
,'Monday' | |
,'Saturday' | |
,'Sunday' | |
,'Thursday' | |
,'Tuesday' | |
,'Wednesday' | |
,'fk_neighbourhoods' | |
,'local_date') | |
) | |
,mycteSaturday as ( | |
SELECT | |
unpvt.Value grp | |
,unpvt1.[key], unpvt1.[value] as Saturday | |
FROM mycte AS src | |
CROSS APPLY OpenJson(src.TheValue) AS unpvt | |
cross apply OpenJson(JSON_VALUE(src.TheValue,'$.Saturday')) AS unpvt1 | |
WHERE unpvt.Type !=5 and | |
unpvt.[Key] not in ( | |
'Friday' | |
,'Monday' | |
,'Saturday' | |
,'Sunday' | |
,'Thursday' | |
,'Tuesday' | |
,'Wednesday' | |
,'fk_neighbourhoods' | |
,'local_date') | |
) | |
,mycteSunday as ( | |
SELECT | |
unpvt.Value grp | |
,unpvt1.[key], unpvt1.[value] as Sunday | |
FROM mycte AS src | |
CROSS APPLY OpenJson(src.TheValue) AS unpvt | |
cross apply OpenJson(JSON_VALUE(src.TheValue,'$.Sunday')) AS unpvt1 | |
WHERE unpvt.Type !=5 and | |
unpvt.[Key] not in ( | |
'Friday' | |
,'Monday' | |
,'Saturday' | |
,'Sunday' | |
,'Thursday' | |
,'Tuesday' | |
,'Wednesday' | |
,'fk_neighbourhoods' | |
,'local_date') | |
) | |
,mycteThursday as ( | |
SELECT | |
unpvt.Value grp | |
,unpvt1.[key], unpvt1.[value] as Thursday | |
FROM mycte AS src | |
CROSS APPLY OpenJson(src.TheValue) AS unpvt | |
cross apply OpenJson(JSON_VALUE(src.TheValue,'$.Thursday')) AS unpvt1 | |
WHERE unpvt.Type !=5 and | |
unpvt.[Key] not in ( | |
'Friday' | |
,'Monday' | |
,'Saturday' | |
,'Sunday' | |
,'Thursday' | |
,'Tuesday' | |
,'Wednesday' | |
,'fk_neighbourhoods' | |
,'local_date') | |
) | |
,mycteTuesday as ( | |
SELECT | |
unpvt.Value grp | |
,unpvt1.[key], unpvt1.[value] as Tuesday | |
FROM mycte AS src | |
CROSS APPLY OpenJson(src.TheValue) AS unpvt | |
cross apply OpenJson(JSON_VALUE(src.TheValue,'$.Tuesday')) AS unpvt1 | |
WHERE unpvt.Type !=5 and | |
unpvt.[Key] not in ( | |
'Friday' | |
,'Monday' | |
,'Saturday' | |
,'Sunday' | |
,'Thursday' | |
,'Tuesday' | |
,'Wednesday' | |
,'fk_neighbourhoods' | |
,'local_date') | |
) | |
,mycteWednesday as ( | |
SELECT | |
unpvt.Value grp | |
,unpvt1.[key], unpvt1.[value] as Wednesday | |
FROM mycte AS src | |
CROSS APPLY OpenJson(src.TheValue) AS unpvt | |
cross apply OpenJson(JSON_VALUE(src.TheValue,'$.Wednesday')) AS unpvt1 | |
WHERE unpvt.Type !=5 and | |
unpvt.[Key] not in ( | |
'Friday' | |
,'Monday' | |
,'Saturday' | |
,'Sunday' | |
,'Thursday' | |
,'Tuesday' | |
,'Wednesday' | |
,'fk_neighbourhoods' | |
,'local_date') | |
) | |
select t1.fk_neighbourhoods, | |
t1.local_date | |
--,t1.grp | |
,t1.Friday | |
,t2.Monday | |
,t3.Saturday | |
,t4.Sunday | |
,t5.Thursday | |
,t6.Tuesday | |
,t7.Wednesday | |
from mycteFriday t1 | |
join mycteMonday t2 on t1.grp=t2.grp and t1.[key]=t2.[key] | |
join mycteSaturday t3 on t1.grp=t3.grp and t1.[key]=t3.[key] | |
join mycteSunday t4 on t1.grp=t4.grp and t1.[key]=t4.[key] | |
join mycteThursday t5 on t1.grp=t5.grp and t1.[key]=t5.[key] | |
join mycteTuesday t6 on t1.grp=t6.grp and t1.[key]=t6.[key] | |
join mycteWednesday t7 on t1.grp=t7.grp and t1.[key]=t7.[key] | |
drop table #tmpTable | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment