使用动态列和列名进行逆透视 [英] unpivot with dynamic columns plus column names

问题描述

我正在尝试使用以下格式对包含大量列的表进行逆透视:

I'm trying to unpivot a table with a large number of columns in the format of:

PID UID col1 col2 col3...

下面的动态 SQL 将为我提供除列名之外的几乎所有内容.目标是在ID"字段中填写逆透视值源自的列的名称.

The dynamic SQL below will get me almost everything except the name of the column. The goal is to fill in the "ID" field with the name of the column from which the unpivot value originated.

-- 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，然后再进行一次逆透视，但我一直无法弄清楚.

I thought maybe I could do some sort of join with syscolumns & MyTable and then do a second unpivot but I haven't been able to figure it out.

最终我的查询应该返回

PID UID ID          Val

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

所以虽然我知道如何获取列的名称以便为 unpivot 生成动态 SQL，但我不知道如何将列的名称加入到 unpivot 的输出中.

So while I know how to get the name of the columns in order to generate the dynamic SQL for the unpivot, I don't know how to join the name of the columns into the output of the unpivot.

推荐答案

您可以从 unpivot 的 val for col in 部分引用列名.col获取列名

You can reference the column name from the val for col in part of the unpivot. Col gets the column name

小提琴示例

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

这篇关于使用动态列和列名进行逆透视的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！