SQLServer: 列到行

寻找将列转换为行的优雅(或任何)解决方案。

下面是一个示例: 我有一个包含以下模式的表:

[ID] [EntityID] [Indicator1] [Indicator2] [Indicator3] ... [Indicator150]

以下是我希望得到的结果:

[ID] [EntityId] [IndicatorName] [IndicatorValue]

结果将是:

1 1 'Indicator1' 'Value of Indicator 1 for entity 1'
2 1 'Indicator2' 'Value of Indicator 2 for entity 1'
3 1 'Indicator3' 'Value of Indicator 3 for entity 1'
4 2 'Indicator1' 'Value of Indicator 1 for entity 2'

等等。

这说得通吗？你有什么建议在哪里寻找和如何在 T-SQL 中完成它？

394326 次浏览
小开
最佳答案

可以使用 UNPIVOT函数将列转换为行:

select id, entityId,
indicatorname,
indicatorvalue
from yourtable
unpivot
(
indicatorvalue
for indicatorname in (Indicator1, Indicator2, Indicator3)
) unpiv;

注意，要取消枢轴的列的数据类型必须相同，因此在应用取消枢轴之前可能必须转换数据类型。

您还可以使用 CROSS APPLY和 UNION ALL 来转换列:

select id, entityid,
indicatorname,
indicatorvalue
from yourtable
cross apply
(
select 'Indicator1', Indicator1 union all
select 'Indicator2', Indicator2 union all
select 'Indicator3', Indicator3 union all
select 'Indicator4', Indicator4
) c (indicatorname, indicatorvalue);

根据您的 SQLServer 版本，您甚至可以使用带有 VALUES 子句的 CROSSAPPY:

select id, entityid,
indicatorname,
indicatorvalue
from yourtable
cross apply
(
values
('Indicator1', Indicator1),
('Indicator2', Indicator2),
('Indicator3', Indicator3),
('Indicator4', Indicator4)
) c (indicatorname, indicatorvalue);

最后，如果您有150列要取消数据透视，并且您不想硬编码整个查询，那么您可以使用动态 SQL 生成 SQL 语句:

DECLARE @colsUnpivot AS NVARCHAR(MAX),
@query  AS NVARCHAR(MAX)


select @colsUnpivot
= stuff((select ','+quotename(C.column_name)
from information_schema.columns as C
where C.table_name = 'yourtable' and
C.column_name like 'Indicator%'
for xml path('')), 1, 1, '')


set @query
= 'select id, entityId,
indicatorname,
indicatorvalue
from yourtable
unpivot
(
indicatorvalue
for indicatorname in ('+ @colsunpivot +')
) u'


exec sp_executesql @query;
小开

如果你有150列，那么我认为 UNPIVOT 不是一个选项。因此可以使用 xml 技巧

;with CTE1 as (
select ID, EntityID, (select t.* for xml raw('row'), type) as Data
from temp1 as t
), CTE2 as (
select
C.id, C.EntityID,
F.C.value('local-name(.)', 'nvarchar(128)') as IndicatorName,
F.C.value('.', 'nvarchar(max)') as IndicatorValue
from CTE1 as c
outer apply c.Data.nodes('row/@*') as F(C)
)
select * from CTE2 where IndicatorName like 'Indicator%'

sql fiddle demo

您也可以编写动态 SQL，但是我更喜欢 xml-对于动态 SQL，您必须拥有直接从表中选择数据的权限，这并不总是一个选项。

UPDATE < br > 由于注释中的火焰很大，我想我会添加一些 xml/Dynamic SQL 的优缺点。我会尽量保持客观，不提优雅和丑陋。如果你有任何其他的优缺点，编辑答案或写在评论

缺点

  • 它是 没那么快作为动态 SQL，粗略测试告诉我 xml 比动态 SQL 慢2.5倍(这是对 ~ 250000行表的一个查询，所以这个估计并不准确)。如果您愿意，您可以自己比较它，这里是 强示例，在100000行上，它是29s (xml) vs 14s (Dynamic) ;
  • 对于不熟悉 xpath 的人来说，可能是 很难理解;

专业人士

  • 它是 同样的范围作为您的其他查询，这可能是非常方便的
    • 你可以在你的 触发器中查询 inserted和 deleted表(动态的根本不可能) ;
    • 用户不必从表中直接选择 权限。我的意思是，如果你有存储过程层和用户有权限运行 sp，但没有权限直接查询表，你仍然可以在存储过程中使用这个查询;
    • 你可以在你的作用域中填充 查询表变量查询表变量(要在动态 SQL 中传递它，你必须要么使它成为临时表，要么创建类型并将它作为一个参数传递给动态 SQL;
  • 你可以这样做 函数中的查询(标量或表值)。不可能在函数内部使用动态 SQL;
小开 
DECLARE @TableName varchar(max)=NULL
SELECT @TableName=COALESCE(@TableName+',','')+t.TABLE_CATALOG+'.'+ t.TABLE_SCHEMA+'.'+o.Name
FROM sysindexes AS i
INNER JOIN sysobjects AS o ON i.id = o.id
INNER JOIN INFORMATION_SCHEMA.TABLES T ON T.TABLE_NAME=o.name
WHERE i.indid < 2
AND OBJECTPROPERTY(o.id,'IsMSShipped') = 0
AND i.rowcnt >350
AND o.xtype !='TF'
ORDER BY o.name ASC


print @tablename

您可以获得行数大于350的表的列表。您可以在表的解决方案列表中看到行。

小开

我需要一个解决方案，在不知道列名(在触发器中使用)和不知道动态 sql (动态 sql 在触发器中使用太慢)的情况下，将列转换为 Microsoft SQL Server 行。

我最终找到了这个解决方案，效果不错:

SELECT
insRowTbl.PK,
insRowTbl.Username,
attr.insRow.value('local-name(.)', 'nvarchar(128)') as FieldName,
attr.insRow.value('.', 'nvarchar(max)') as FieldValue
FROM ( Select
i.ID as PK,
i.LastModifiedBy as Username,
convert(xml, (select i.* for xml raw)) as insRowCol
FROM inserted as i
) as insRowTbl
CROSS APPLY insRowTbl.insRowCol.nodes('/row/@*') as attr(insRow)

如您所见，我将该行转换为 XML (Subquery select i，* for XML raw，这将把所有列转换为一个 XML 列)

然后，对该列的每个 XML 属性交叉使用一个函数，以便每个属性获得一行。

总的来说，这样可以在不知道列名和不使用动态 sql 的情况下将列转换为行。对我来说已经足够快了。

(编辑: 我刚刚看到罗曼 · 皮卡在上面回答，他也在做同样的事情。 我首先使用带有游标的动态 sql 触发器，它比这个解决方案慢10到100倍，但是它可能是由游标引起的，而不是由动态 sql 引起的。无论如何，这个解决方案是非常简单的一个普遍的，所以它肯定是一个选项)。

我在这里留下这个评论，因为我想在我的文章中引用这个关于完整审计触发器的解释，你可以在这里找到: https://stackoverflow.com/a/43800286/4160788

小开

为了帮助新读者，我创建了一个例子来更好地理解@blufeet 关于 UNPIVOT 的答案。

 SELECT id
,entityId
,indicatorname
,indicatorvalue
FROM (VALUES
(1, 1, 'Value of Indicator 1 for entity 1', 'Value of Indicator 2 for entity 1', 'Value of Indicator 3 for entity 1'),
(2, 1, 'Value of Indicator 1 for entity 2', 'Value of Indicator 2 for entity 2', 'Value of Indicator 3 for entity 2'),
(3, 1, 'Value of Indicator 1 for entity 3', 'Value of Indicator 2 for entity 3', 'Value of Indicator 3 for entity 3'),
(4, 2, 'Value of Indicator 1 for entity 4', 'Value of Indicator 2 for entity 4', 'Value of Indicator 3 for entity 4')
) AS Category(ID, EntityId, Indicator1, Indicator2, Indicator3)
UNPIVOT
(
indicatorvalue
FOR indicatorname IN (Indicator1, Indicator2, Indicator3)
) UNPIV;
小开

只是因为我没有看到它提到。

如果是2016 + ，这里还有另一个选项，可以在不实际使用动态 SQL 的情况下动态取消数据轴。

例子

Declare @YourTable Table ([ID] varchar(50),[Col1] varchar(50),[Col2] varchar(50))
Insert Into @YourTable Values
(1,'A','B')
,(2,'R','C')
,(3,'X','D')


Select A.[ID]
,Item  = B.[Key]
,Value = B.[Value]
From  @YourTable A
Cross Apply ( Select *
From  OpenJson((Select A.* For JSON Path,Without_Array_Wrapper ))
Where [Key] not in ('ID','Other','Columns','ToExclude')
) B

报税表

ID  Item    Value
1   Col1    A
1   Col2    B
2   Col1    R
2   Col2    C
3   Col1    X
3   Col2    D
小开

与此相反的是将一根柱子压平成一个 CSV

从 STRING _ SPLIT (‘ Akio，Hiraku，Kazuo’,’,’)中选择 STRING _ AGG ([ value ] ,’,’)


提交答案