T-SQL Unpivot with JSON

T-SQL Unpivot with JSON.sql

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