sql-server - 如屏幕截图所示取消数据透视表

Question

我有这个数据透视表

Particular_LL   Dr_LL    Cr_LL      Particular_CLL      Dr_CLL      Cr_CLL
------------------------------------------------------------------------------
Loan         3000000.00 3000000.00  Bank A/C OverDraft  100000.00   100000.00

我尝试使用此查询来取消透视表，但没有得到正确的输出：

SELECT 
    particular,
    debit,
    credit
FROM
    dbo.Test1 
UNPIVOT
    (particular
     FOR col1 IN ([Particular_LL], [Particular_CLL])) u 
UNPIVOT 
    (debit
     FOR col2 IN (Dr_LL, Dr_CLL)) u 
UNPIVOT 
    (credit
     FOR col3 IN (Cr_LL, Cr_CLL)) u
WHERE 
    right(col1, 2) = right(col2, 2)
    AND right(col1, 2) = right(col3, 2)

我得到这个结果：

particular            debit          credit
----------------------------------------------
Loan                  3000000.00    3000000.00
Loan                  3000000.00     100000.00
Loan                   100000.00    3000000.00
Loan                   100000.00     100000.00
Bank A/C OverDraft    3000000.00    3000000.00
Bank A/C OverDraft    3000000.00     100000.00
Bank A/C OverDraft     100000.00    3000000.00
Bank A/C OverDraft     100000.00     100000.00

我需要这个输出

particular          debit       credit
-------------------------------------------
Loan               3000000.00   3000000.00
Bank A/C OverDraft  100000.00    100000.00

标签： sql-serversql-server-2014

Answer 1

你可以使用这个。

SELECT 
    particular,
    debit,
    credit  
FROM
    dbo.Test1 
UNPIVOT
    (particular
     FOR col1 IN ([Particular_LL], [Particular_CLL])) u 
UNPIVOT 
    (debit
     FOR col2 IN (Dr_LL, Dr_CLL)) u 
UNPIVOT 
    (credit
     FOR col3 IN (Cr_LL, Cr_CLL)) u
WHERE 
    REPLACE(col1, 'Particular_',  '') = REPLACE(col2, 'Dr_',  '')
    AND REPLACE(col1, 'Particular_',  '') = REPLACE(col3, 'Cr_',  '')