SQL - Defined vs Dynamic table column values into JSON

 Convert Defined vs Dynamic table column values into JSON using SQL Syntax, using SQL Pivot and Subquery execution achieved to construct JSON,

IF OBJECT_ID('tempdb..#tempDefinedTable1') IS NOT NULL

       DROP TABLE #tempDefinedTable1

IF OBJECT_ID('tempdb..#tempDefinedTable2') IS NOT NULL

       DROP TABLE #tempDefinedTable2

IF OBJECT_ID('tempdb..#tempAllColumns') IS NOT NULL

       drop table #tempAllColumns

IF OBJECT_ID('tempdb..#tempMatchedColumns') IS NOT NULL

       drop table #tempMatchedColumns

IF OBJECT_ID('tempdb..#tempUnMatchedColumns') IS NOT NULL

       drop table #tempUnMatchedColumns

 

--create some defined column names as values

 create table #tempDefinedTable1 (

 definedcolnames varchar(max)

)

insert into #tempDefinedTable1

values ('col1'),('col2')

 

select * from #tempDefinedTable1

 

-- create table with defined columns + dynamic columns

create table #tempDefinedTable2 (

id int,

col1 varchar(max),

col2 varchar(max),

dynamicCol1 varchar(max),

dynamicCol2 varchar(max)

)

 

insert into #tempDefinedTable2

values (1, 'a1', 'b1', 'c1', 'd1'),(2, 'a2', 'b2', 'c2', 'd2')

 

select * from #tempDefinedTable2

  

--From the defined+dynamic column list, consolidate columns as values

Select C.* into #tempAllColumns

 From #tempDefinedTable2 A

 Cross Apply ( values (cast((Select A.* for XML RAW) as xml))) B(XMLData)

 Cross Apply (

                Select ColumnName  = a.value('local-name(.)','varchar(100)')

                      ,ColumnValue = a.value('.','varchar(max)')

                                     ,Id

                 From  B.XMLData.nodes('/row')  as C1(n)

                 Cross Apply C1.n.nodes('./@*') as C2(a)

                 Where a.value('local-name(.)','varchar(100)') not in ('ID','ExcludeOtherCol')

             ) C

 

select Id, ColumnName, ColumnValue from #tempAllColumns

 

--now preparing sql sub query to list defined columns in root level, dynamic columns as nested object

DECLARE @Columns as VARCHAR(MAX)

 

SELECT @Columns = COALESCE(@Columns + ', ','') + QUOTENAME([ColumnName])

FROM

    (SELECT DISTINCT ColumnName FROM #tempAllColumns st

       JOIN #tempDefinedTable1 tt on tt.definedcolnames = st.ColumnName

       WHERE tt.definedcolnames is not null) AS B

ORDER BY B.ColumnName

 

select @Columns as DefinedColumns

 

 

DECLARE @DynamicColumns as VARCHAR(MAX)

SELECT @DynamicColumns = COALESCE(@DynamicColumns + ', ','') + QUOTENAME([ColumnName])

FROM

    (SELECT DISTINCT ColumnName FROM #tempAllColumns st

       LEFT JOIN #tempDefinedTable1 tt on tt.definedcolnames = st.ColumnName

       WHERE tt.definedcolnames is null) AS B

ORDER BY B.ColumnName

select @DynamicColumns  as UndefinedColumns

 

DECLARE @SQL as VARCHAR(MAX)

SET @SQL = 'SELECT Id,' + @Columns + ',

       (SELECT PR1.Id,' + @DynamicColumns + ' FROM (select st1.Id, st1.[ColumnName], st1.ColumnValue from #tempAllColumns st1

        LEFT JOIN #tempDefinedTable1 tt1 on tt1.definedcolnames = st1.ColumnName

        WHERE tt1.definedcolnames is null) as PD2

        PIVOT (

          max(ColumnValue)

          FOR [ColumnName] IN (' + @DynamicColumns + ')

       ) AS PR1

       WHERE PR1.Id = PR.Id

       ORDER BY Id

       FOR JSON PATH, INCLUDE_NULL_VALUES) as ExtraFields

FROM

(

 select Id,[ColumnName],ColumnValue from #tempAllColumns st

 JOIN #tempDefinedTable1 tt on tt.definedcolnames = st.ColumnName

 WHERE tt.definedcolnames is not null

) as PD

PIVOT

(

   max(ColumnValue)

   FOR [ColumnName] IN (' + @Columns + ')

) AS PR

ORDER BY Id

FOR JSON PATH, INCLUDE_NULL_VALUES'

 

EXEC(@SQL);

 

Columns:

Output: