时间戳

首页 > 解决方案 > 如何修复“来自同一ID的所有数据到一行”？

sql-server - 如何修复“来自同一ID的所有数据到一行”？

问题描述

我正在尝试使用Case语句将行转换为列并且它有效，但我有重复的 ID。

000001  NULL    NULL    NULL    12075000.000    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL
000001  NULL    NULL    NULL    NULL    10500000.000    NULL    NULL    NULL    NULL    NULL    NULL    NULL
000001  NULL    NULL    NULL    NULL    NULL    10500000.000    NULL    NULL    NULL    NULL    NULL    NULL
000001  NULL    NULL    NULL    NULL    NULL    NULL    10500000.000    NULL    NULL    NULL    NULL    NULL
000001  NULL    NULL    NULL    NULL    NULL    NULL    NULL    10500000.000    NULL    NULL    NULL    NULL
000001  NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    10500000.000    NULL    NULL    NULL

第一列是id列，其他列代表月份。如您所见，id's 是相同的。有没有办法在所有 12 个月的每个 id 的一行中显示数据？

SELECT CID,
    (CASE WHEN EOMDATE = '2018-08-16' THEN BALANCE end) AS SHRAWAN,
    (CASE WHEN EOMDATE = '2018-09-16' THEN BALANCE end) AS BHADAU,
    (CASE WHEN EOMDATE = '2018-10-17' THEN BALANCE end) AS ASHOJ,
    (CASE WHEN EOMDATE = '2018-11-16' THEN BALANCE end) AS KARTIK,
    (CASE WHEN EOMDATE = '2018-12-15' THEN BALANCE end) AS MANGSHIR,
    (CASE WHEN EOMDATE = '2019-01-14' THEN BALANCE end) AS POUSH,
    (CASE WHEN EOMDATE = '2019-02-12' THEN BALANCE end) AS MAGH,
    (CASE WHEN EOMDATE = '2019-03-14' THEN BALANCE end) AS FALGUN,
    (CASE WHEN EOMDATE = '2019-04-13' THEN BALANCE end) AS CHAITRA,
    (CASE WHEN EOMDATE = '2019-05-14' THEN BALANCE end) AS BAISHAKH,
    (CASE WHEN EOMDATE = '2019-06-16' THEN BALANCE end) AS JETH,
    (CASE WHEN EOMDATE = '2019-07-17' THEN BALANCE end) AS ASHAD
    FROM 
    (SELECT R.BR, R.CID, E.EOMDate, SUM(E.BALANCEAMT) AS BALANCE
    FROM T_EOMHIST E 
        LEFT JOIN T_RELACC R ON E.Acc = R.Acc AND E.BR = R.BR AND E.APPTYPE IN ('1','3')
        LEFT JOIN T_GLLINK G ON G.BR = E.BR AND G.Code = E.GlCode AND G.CoopRep01 = 'Y' and G.TableId in ('10','30')
        LEFT JOIN T_GLCONTROL H ON H.BR = E.BR 
    WHERE H.FinYear = '2018' AND E.EOMDate BETWEEN H.FinYrStartDate AND H.FinYrEndDate 
        AND R.TYPE = '010' AND R.AppType IN ('1','3') 
        AND CID = '000001'
        GROUP BY R.BR, R.CID, E.EOMDATE)T 
    WHERE CID = '000001'

这是选择查询的代码。

预期结果是同一列的单行记录。仅显示NULL没有任何数据。我也尝试过使用该GROUP BY子句，但它显示了一些错误，说聚合函数。

标签： sql-server

解决方案

SELECT CID,
    SUM(ISNULL(CASE WHEN EOMDATE = '2018-08-16' THEN BALANCE end, 0)) AS SHRAWAN,
    SUM(ISNULL(CASE WHEN EOMDATE = '2018-09-16' THEN BALANCE end, 0)) AS BHADAU,
    SUM(ISNULL(CASE WHEN EOMDATE = '2018-10-17' THEN BALANCE end, 0)) AS ASHOJ,
    SUM(ISNULL(CASE WHEN EOMDATE = '2018-11-16' THEN BALANCE end, 0)) AS KARTIK,
    SUM(ISNULL(CASE WHEN EOMDATE = '2018-12-15' THEN BALANCE end, 0)) AS MANGSHIR,
    SUM(ISNULL(CASE WHEN EOMDATE = '2019-01-14' THEN BALANCE end, 0)) AS POUSH,
    SUM(ISNULL(CASE WHEN EOMDATE = '2019-02-12' THEN BALANCE end, 0)) AS MAGH,
    SUM(ISNULL(CASE WHEN EOMDATE = '2019-03-14' THEN BALANCE end, 0)) AS FALGUN,
    SUM(ISNULL(CASE WHEN EOMDATE = '2019-04-13' THEN BALANCE end, 0)) AS CHAITRA,
    SUM(ISNULL(CASE WHEN EOMDATE = '2019-05-14' THEN BALANCE end, 0)) AS BAISHAKH,
    SUM(ISNULL(CASE WHEN EOMDATE = '2019-06-16' THEN BALANCE end, 0)) AS JETH,
    SUM(ISNULL(CASE WHEN EOMDATE = '2019-07-17' THEN BALANCE end, 0)) AS ASHAD
FROM FROM 
    ...
GROUP BY CID

或者，如果您需要 NULL 而不是 0：

NULLIF(SUM(ISNULL(CASE WHEN EOMDATE = '2018-08-16' THEN BALANCE end, 0)), 0) AS SHRAWAN

推荐阅读