By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
DECLARE @json nvarchar(max) = '{"value": [
{
"AEDAT": "20211119",
"ERDAT": "20211119"
},
{
"AEDAT": "20211119",
"ERDAT": "20211112"
},
{
"AEDAT": "20211123",
"ERDAT": "20211123"
},
{
"AEDAT": "00000000",
"ERDAT": "20211119"
},
{
"AEDAT": "00000000",
"ERDAT": "20211123"
}]}';
DECLARE @ColumnName varchar(1000) = 'AEDAT,ERDAT';
DECLARE @col_table TABLE (ColumnName varchar(100) COLLATE Latin1_General_BIN2 PRIMARY KEY)
INSERT INTO @col_table (ColumnName)
SELECT value
FROM STRING_SPLIT(@ColumnName,',');
SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),
j2.[key],
j2.value
FROM OPENJSON(@json, '$.value') j1
CROSS APPLY OPENJSON(j1.value) j2
WHERE CAST(j2.[key] AS varchar(100)) IN (
| (No column name) | key | value |
|---|---|---|
| 1 | AEDAT | 20211119 |
| 2 | ERDAT | 20211119 |
| 3 | AEDAT | 20211119 |
| 4 | ERDAT | 20211112 |
| 5 | AEDAT | 20211123 |
| 6 | ERDAT | 20211123 |
| 7 | AEDAT | 00000000 |
| 8 | ERDAT | 20211119 |
| 9 | AEDAT | 00000000 |
| 10 | ERDAT | 20211123 |