T-SQL 中的多列透视 [英] Multiple Column Pivot in T-SQL
本文介绍了T-SQL 中的多列透视的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在处理一个表格,其中有多行需要转入列.因此,枢轴是解决此问题的完美解决方案,并且当我只需要一个字段时效果很好.我需要根据枢轴返回几个字段.这是去除了细节的伪代码:
I am working with a table where there are multiple rows that I need pivoted into columns. So the pivot is the perfect solution for this, and works well when all I need is one field. I am needing to return several fields based upon the pivot. Here is the pseudo code with specifics stripped out:
SELECT
field1,
[1], [2], [3], [4]
FROM
(
SELECT
field1,
field2,
(ROW_NUMBER() OVER(PARTITION BY field1 ORDER BY field2)) RowID
FROM tblname
) AS SourceTable
PIVOT
(
MAX(field2)
FOR RowID IN ([1], [2], [3], [4])
) AS PivotTable;
上述语法非常有效,但是当我需要获取 field3、field4..... 中的其他信息时该怎么办?
The above syntax works brilliantly, but what do I do when I need to get additional information found in field3, field4....?
推荐答案
使用 MAX(CASE...) 和 GROUP BY 重写:
Rewrite using MAX(CASE...) and GROUP BY:
select
field1
, [1] = max(case when RowID = 1 then field2 end)
, [2] = max(case when RowID = 2 then field2 end)
, [3] = max(case when RowID = 3 then field2 end)
, [4] = max(case when RowID = 4 then field2 end)
from (
select
field1
, field2
, RowID = row_number() over (partition by field1 order by field2)
from tblname
) SourceTable
group by
field1
从那里您可以添加 field3、field4 等.
From there you can add in field3, field4, etc.
这篇关于T-SQL 中的多列透视的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文