T-SQL Server dynamic UNPIVOT data original columns

Question

Hi
I am trying to make a location(x, y) with merging tables into a single one.
X is found, Y is only found outside of the dynamic query but I cannot seem to map it with the unpivoted result...
I would like to have the original column_id attached to the resulted unpivoted query.

Here is my script:
DECLARE @strQry VARCHAR(8000)
SELECT @strQry =
'SELECT [Row_ID], [Field_Name]
FROM (
SELECT [Row_ID] = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)), ' + p.[Column_Name] + '
FROM [##tbl' + p.[Table_Name] + ']
) x UNPIVOT ([Field_Name] FOR [Field_Data] IN (' + p.[Column_Name] + ')) u'
FROM (
SELECT [Table_Name] = t.name, [Column_ID] = STRING_AGG(CAST(c.column_id AS VARCHAR(12)), ', '), [Column_Name] = STRING_AGG('[' + c.name + ']', ', ')
FROM tempdb.sys.table t
LEFT JOIN tempdb.sys.columns c ON t.object_id = c.object_id
GROUP BY t.name
) p
PRINT (@strQry)
EXEC (@strQry)

/How to get [Column_ID] to fit with [Field_Name] according to their original [Column_Name] during UNPIVOT?/
/*
Original:
| 1 | 2 | 3 |
1 | a | b | c |
2 | d | e | f |
3 | a | b | c |

Unpivoted:
| x | | y |
1 | a | Original column_id was: | 1 |
2 | b | Original column_id was: | 2 |
3 | c | Original column_id was: | 3 |
4 | d | Original column_id was: | 1 |
5 | e | Original column_id was: | 2 |
6 | f | Original column_id was: | 3 |
7 | a | Original column_id was: | 1 |
8 | b | Original column_id was: | 2 |
9 | c | Original column_id was: | 3 |
*/

For those working with SQL Server v16-:
SELECT [Table_Name] = t.name, [Column_Name] = STUFF((
SELECT TOP 60 ', [' + c.name + ']'
FROM tempdb.sys.columns c
WHERE MIN(t.object_id) = c.object_id
GROUP BY c.name
/ORDER BY MIN(c.column_id)/
FOR XML PATH(''), TYPE).value('.[1]', 'VARCHAR(MAX)'), 1, 2, '')
FROM tempdb.sys.tables t
GROUP BY t.name

Answer 1

The unpivot column should have the same(compatible) data type.
You should include your sample table DDL to get the code you need.
Here is the fix by guessing:

 DECLARE @strQry VARCHAR(8000)  
SELECT @strQry =  
'SELECT [Row_ID], [Field_Name]  
FROM (  
SELECT [Row_ID] = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)), ' + p.[Column_Name2] + '  
FROM [' + p.[Table_Name] + ']  
) x UNPIVOT ([Field_Name] FOR [Field_Data] IN (' + p.[Column_Name] + ')) u'  
FROM (  
SELECT [Table_Name] = t.name, [Column_ID] = STRING_AGG(CAST(c.column_id AS VARCHAR(12)), ', ')  
, [Column_Name] = STRING_AGG('[' + c.name + ']', ', ')  
, [Column_Name2] = STRING_AGG('try_cast([' + c.name + '] as varchar(50)) as [' + c.name + ']', ', ')  
FROM tempdb.sys.tables t  
LEFT JOIN tempdb.sys.columns c ON t.object_id = c.object_id  
GROUP BY t.name  
) p  
PRINT (@strQry)  
EXEC (@strQry)