JSON_Unpivot_Pivot.sql

create table FruitsTest (theDate date,Apple int, Kiwi int, Grape int, Orange int, Banana int)
Insert into FruitsTest values 
('2022-06-10',2,2,4,3,1),
('2022-06-11',3,2,5,2,1)

 
 ;with mycte   (TheKey, TheValue) as (
SELECT [KEY], Value from OpenJson((
select * from FruitsTest  FOR JSON AUTO ))
WHERE type = 5)

 
 ,mycte2 as (
 SELECT
 --columns not in unpivot list
 JSON_VALUE(src.TheValue,'$.theDate') theDate
-- ,src.TheKey +1 as colOrder, 
 ,unpvt.[Key] theKey, unpvt.Value theValue
 ,unpvt.Value-Avg(try_cast(unpvt.Value as decimal(5,1)))
 Over(Partition by unpvt.[Key]) delta
 FROM mycte AS src
 CROSS APPLY OpenJson(src.TheValue) AS unpvt  
 WHERE unpvt.Type!=5  and
 --columns not in unpivot list
 unpvt.[Key] not in ('theDate')
 )

 select theDate,theKey,theValue
 into mytemp
 from mycte2
 where delta<>0

 
 Declare @sqlCase as NVarchar(4000) =null
Declare @sql as NVarchar(4000)=null
 
  
declare @ColumnHeaders VARCHAR(MAX), @ColumnHeaders1 VARCHAR(MAX);
set @ColumnHeaders = STUFF( (SELECT DISTINCT ',' + 'Max(CASE WHEN [theKey]=' + quotename(theKey,'''') + ' THEN theValue else null end ) as ' + quotename(theKey,'[')  + char(10)+char(13)
                             FROM mytemp
                             FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '');
  --print @ColumnHeaders
 
Set @sql  =' Select theDate,'+   @ColumnHeaders 
+ ' from mytemp Group by theDate ';
     
print @sql
EXEC(@SQL)

Drop table mytemp
drop table FruitsTest