将行高效地透视/转置为具有多列的列
假设我有如下数据:
Table
Num1 Type1 Code Group DA Account Value
1X2 GG XX1 INTS 1 123 75.00
1X2 GG XX1 INTS 1 234 100.00
我要做的是透视数据,使其如下所示:
Num1 Type1 Code Group DA 123 234
1X2 GG XX1 INTS 1 75.00 100.00
我不太确定如何使用Pivot来完成这样的事情,但我确实尝试了以下方法:
Select Num1,
Type1,
Code,
Group,
DA,
'123' = (Select Value from Table t2 where t1.num1 = t2.num1 and Account = 123 ),
'234' = (Select Value from Table t2 where t1.num1 = t2.num1 and Account = 234 )
From Table t1
但是,我收到的错误是:Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
在这种情况下,我将";Top 1";添加到每个子查询:
Select Num1,
Type1,
Code,
Group,
DA,
'123' = (Select TOP 1 Value from Table t2 where t1.num1 = t2.num1 and Account = 123 ),
'234' = (Select TOP 1 Value from Table t2 where t1.num1 = t2.num1 and Account = 234 )
From Table t1
但是,尽管查询现在返回两行每个帐户的金额,我并不完全理解TOP 1的用途,但基本上现在的数据如下:
Num1 Type1 Code Group DA 123 234
1X2 GG XX1 INTS 1 NULL 100.00
1X2 GG XX1 INTS 1 75.00 NULL
我想这还不错,因为我可以对所有其他列执行MAX(123)
和MAX(234)
,最后得到1行。
有没有更好的方法来解决这个问题?这可以通过Pivot实现吗?
解决方案
SELECT
[Num1],
[Type1],
[Code],
[Group],
[DA],
[123],
[234]
FROM
yourTable
PIVOT
(
MAX([value])
FOR [account] IN ([123], [234])
)
AS PivotTable
https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=7fbe16b9254aa5ee60a23e43eec9597f
相关文章