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);
