动态列加列列名称 [英] 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 

我以为也许我可以做某种加入系列和MyTable然后做一个第二个univot，但我无法弄清楚。

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

所以当我知道如何获取列的名称，以生成动态SQL for Univot，我不知道如何将列的名称加入到不透明的输出中。

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.

推荐答案

您可以从 val中的 val引用部分的不透明度。 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屋！