具有旋转数据的阶梯行为

Question

向其他程序员和查询编写者致敬，

我有一个漂亮的查询

SELECT ID, [1] AS coL1, [15] AS coL2, [2] AS coL3, [16] AS coL4, [12] AS coL5
FROM MY_TABLE
PIVOT (sum(INT_VALUE) FOR FUND_CODE IN ([1],[2],[15],[16],[12])) AS p
--GROUP BY ID, [1] , [15]  , [2] , [16] , [12] 
ORDER BY ID ASC

，它返回如下数据：

10001      182       NULL      NULL      NULL
10001      NULL      81        NULL      NULL
10001      NULL      NULL      182       NULL
10001      NULL      NULL      NULL      81
10002      165       NULL      NULL      NULL
10002      NULL      73        NULL      NULL
10002      NULL      NULL      165       NULL
10002      NULL      NULL      NULL      73

10001 和 10002 是两个主键，我想像这样显示我的数据：

10001      182       81      182      81
10002      165       73      165      73

我尝试将此评论 GROUP BY 为 no有用。 有什么提示吗？它涉及 COALESCE 吗？

Answer 1

选择ID
,SUM(1) AS 'col1'
,SUM(15) AS 'col2'
,SUM(2) AS 'col3'
,SUM(16) AS 'col4'
,SUM(12) AS 'col5'
FROM 表 GROUP BY ID

Answer 2

万一...

SELECT ID,
SUM(CASE WHEN FUND_CODE = 1 THEN VR_MOVIMENTACAO_QUOTA ELSE 0 END) coL1,
SUM(CASE WHEN FUND_CODE = 15 THEN VR_MOVIMENTACAO_QUOTA ELSE 0 END) coL2,
SUM(CASE WHEN FUND_CODE = 2 THEN VR_MOVIMENTACAO_QUOTA ELSE 0 END) coL3,
SUM(CASE WHEN FUND_CODE = 16 THEN VR_MOVIMENTACAO_QUOTA ELSE 0 END) coL4,
SUM(CASE WHEN FUND_CODE = 12 THEN VR_MOVIMENTACAO_QUOTA ELSE 0 END) coL5

FROM MY_TABLE
GROUP BY ID