T-SQL Server dynamic UNPIVOT data original columns

Gabriel Lafreniere 61 Reputation points
2022-11-05T10:54:26.62+00:00

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

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,596 questions
0 comments No comments
{count} votes

Accepted answer
  1. Jingyang Li 5,891 Reputation points
    2022-11-05T15:29:25.453+00:00

    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)  
    
    0 comments No comments

0 additional answers

Sort by: Most helpful