使用动态列和列名称进行逆透视

我正在尝试取消透视具有大量列的表，其格式为：

PID UID col1 col2 col3...

下面的动态 SQL 将为我提供除列名之外的几乎所有内容。目标是使用逆透视值源自的列的名称填充“ID”字段。

-- Build list of cols we want to unpivot (skip PID & UID)
declare @cols nvarchar(max) 
select @cols = coalesce(@cols+N',', N'') + quotename(c.name) from syscolumns c
inner join sysobjects o on c.id = o.id and o.xtype = 'u'
where o.name = 'MyTable' and c.name not in ('PID', 'UID') order by c.colid

declare @query nvarchar(max)  

select @query = N'
select PID, [UID], ID, Val
from 
    (
    select PID, UID, ''ID'' as ID, ' + @cols + '
    from MyTable
    where UID <> 0
    ) as cp
    unpivot
    (
    Val for Vals in (' + @cols + ')
    ) as up
'
exec sp_executesql @query 

我想也许我可以与 syscolumns 和 MyTable 进行某种连接，然后进行第二次 unpivot，但我一直无法弄清楚。

最终我的查询应该返回

PID UID ID          Val

123 456 'col1 name' 'xyz'
123 456 'col2 name' 'def'
123 333 'col1 name' 'fdf'
...

因此，虽然我知道如何获取列的名称以便为 unpivot 生成动态 SQL，但我不知道如何将列的名称连接到 unpivot 的输出中。

您可以从以下位置引用列名称val for col in逆透视的一部分。 col 获取列名

小提琴示例 http://sqlfiddle.com/#!3/30e3e/2

-- Build list of cols we want to unpivot (skip PID & UID)
declare @cols nvarchar(max) 
select @cols = coalesce(@cols+N',', N'') + quotename(c.name) from syscolumns c
inner join sysobjects o on c.id = o.id and o.xtype = 'u'
where o.name = 'MyTable' and c.name not in ('PID', 'UID') order by c.colid

declare @query nvarchar(max)  

select @query = N'
select PID, [UID], Col as ID, Val
from 
    (
    select PID, UID, ' + @cols + '
    from MyTable
    where UID <> 0
    ) as cp
    unpivot
    (
    Val for Col in (' + @cols + ')
    ) as up
'
exec sp_executesql @query