本文介紹了將行高效地透視/轉(zhuǎn)置為具有多列的列的處理方法,對大家解決問題具有一定的參考價(jià)值,需要的朋友們下面隨著小編來一起學(xué)習(xí)吧!
問題描述
假設(shè)我有如下數(shù)據(jù):
Table
Num1 Type1 Code Group DA Account Value
1X2 GG XX1 INTS 1 123 75.00
1X2 GG XX1 INTS 1 234 100.00
我要做的是透視數(shù)據(jù),使其如下所示:
Num1 Type1 Code Group DA 123 234
1X2 GG XX1 INTS 1 75.00 100.00
我不太確定如何使用Pivot來完成這樣的事情,但我確實(shí)嘗試了以下方法:
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
但是,我收到的錯(cuò)誤是: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";添加到每個(gè)子查詢:
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
但是,盡管查詢現(xiàn)在返回兩行每個(gè)帳戶的金額,我并不完全理解TOP 1的用途,但基本上現(xiàn)在的數(shù)據(jù)如下:
Num1 Type1 Code Group DA 123 234
1X2 GG XX1 INTS 1 NULL 100.00
1X2 GG XX1 INTS 1 75.00 NULL
我想這還不錯(cuò),因?yàn)槲铱梢詫λ衅渌袌?zhí)行MAX(123)
和MAX(234)
,最后得到1行。
有沒有更好的方法來解決這個(gè)問題?這可以通過Pivot實(shí)現(xiàn)嗎?
推薦答案
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
這篇關(guān)于將行高效地透視/轉(zhuǎn)置為具有多列的列的文章就介紹到這了,希望我們推薦的答案對大家有所幫助,