时间戳

首页 > 解决方案 > 在 SQL 中多次旋转同一列

sql - 在 SQL 中多次旋转同一列

问题描述

我已经搜索了该站点，即使关于这个问题有多个问题，我也不能完全理解它们，因为我是 SQL 的新手，或者它们不完全适合我的问题。

所以我有一张这样的桌子：

ID  Label   AmountA AmountB CurrA   CurrB
1   7         400   4        usd    usd
2   7         500   10       eur    eur
3   7         1000  500      usd    usd
1   8         800   1        usd    usd
2   8         9000  0        eur    eur
3   9         100   0        usd    usd

但我需要这样：

ID  AmountA1    CurrAmountA1    AmountA2    CurrAmountA2    AmountA3    CurrAmountA3    AmountB1    CurrAmountB1    AmountB2    CurrAmountB2    AmountB3    CurrAmountB3
 1  400         USD             800         USD             NULL        NULL            4            USD            1            USD            NULL        NULL
 2  500         EUR             9000        EUR             NULL        NULL            10           EUR            0            EUR            NULL        NULL
 3  1000        USD             NULL        NULL            100         USD             500          USD            NULL         NULL           0           USD

我已经设法执行以下代码：

SELECT ID,
isnull([7],0) as AmountA1, 
isnull([8],0) as AmountA2, 
isnull([9],0) as AmountA3, 
FROM 
(SELECT ID, Label, AmountA, AmountB,CurrA, CurrB FROM mytable ) ps
PIVOT
( sum([AmountA]) FOR Label IN
( [7], 
[8], 
[9])) as pvt

我只是让 AmountA 旋转，但我不知道如何包含其他的。

我不知道是否值得说我需要在数百万行上执行此操作，并且 Label 可以有 10 个不同的值，并且我需要它尽可能快。

谢谢

标签： sqlsql-serverpivot-table

解决方案

我只会使用条件聚合：

select id,
       max(case when seqnum = 1 then amountA end) as amountA_1,
       max(case when seqnum = 1 then currA end) as currA_1,
       max(case when seqnum = 2 then amountA end) as amountA_2,
       max(case when seqnum = 2 then currA end) as currA_2,
       max(case when seqnum = 3 then amountA end) as amountA_3,
       max(case when seqnum = 3 then currA end) as currA_3,
       max(case when seqnum = 1 then amountB end) as amountB_1,
       max(case when seqnum = 1 then currB end) as currB_1,
       max(case when seqnum = 2 then amountB end) as amountB_2,
       max(case when seqnum = 2 then currB end) as currB_2,
       max(case when seqnum = 3 then amountB end) as amountB_3,
       max(case when seqnum = 3 then currB end) as currB_3
from (select t.*,
             row_number() over (partition by id order by label) as seqnum
      from t
     ) t
group by id;

推荐阅读