Skip to content

Instantly share code, notes, and snippets.

@jingyang-li
Created July 5, 2020 20:13
Show Gist options
  • Save jingyang-li/2565fca2cd67e4e4d6d8357fb97e1d42 to your computer and use it in GitHub Desktop.
Save jingyang-li/2565fca2cd67e4e4d6d8357fb97e1d42 to your computer and use it in GitHub Desktop.
T-SQL Unpivot with JSON
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