sql - 从 2 列中的同一字段中选择

Question

我想创建一个从两列示例中的同一字段显示的报告：

receipt id     infotype        information  
-----------------------------------------------
1              phone number    123
1              comment         no comment 
2              phone number    12346
2              any comment     price is high 

结果 ：

receipt id    phone number    comment   
----------------------------------------------
1             123             no comment 
2             12346           price is high 

我使用此代码，但结果与我需要的不同：

SELECT 
    RETAILTRANSACTIONINFOCODETRANS.TRANSDATE AS "Date" , 
    RETAILTRANSACTIONTABLE.RECEIPTID AS "Receipt number" ,   
    RETAILTRANSACTIONSALESTRANS.staff  As "Staff" , 
    RETAILTRANSACTIONTABLE.NETAMOUNT AS "Total" , 
    RETAILTRANSACTIONINFOCODETRANS.INFOCODEID AS " Type " , 
    RETAILTRANSACTIONINFOCODETRANS.INFORMATION, 
    MAX(CASE WHEN RETAILTRANSACTIONINFOCODETRANS.INFOCODEID = 'Phone Numb' THEN information END) AS phonenumber,
    MAX(CASE WHEN RETAILTRANSACTIONINFOCODETRANS.INFOCODEID LIKE '%Any Commen%' THEN information END) AS comment
FROM 
    RETAILTRANSACTIONTABLE 
INNER JOIN
    RETAILTRANSACTIONINFOCODETRANS ON RETAILTRANSACTIONINFOCODETRANS.TRANSACTIONID = RETAILTRANSACTIONTABLE.TRANSACTIONID  
INNER JOIN
    RETAILTRANSACTIONSALESTRANS ON RETAILTRANSACTIONSALESTRANS.TRANSACTIONID = RETAILTRANSACTIONINFOCODETRANS.TRANSACTIONID 
GROUP BY 
    GROUPING SETS ((RETAILTRANSACTIONINFOCODETRANS.TRANSDATE,
                    RETAILTRANSACTIONTABLE.RECEIPTID,     
                    RETAILTRANSACTIONSALESTRANS.staff,
                    RETAILTRANSACTIONTABLE.NETAMOUNT,
                    RETAILTRANSACTIONINFOCODETRANS.INFORMATION,
                    RETAILTRANSACTIONINFOCODETRANS.INFOCODEID ),())

标签： sqlsql-server

Answer 1

您可以使用条件聚合

select receiptid, 
       max(case when infotype='phone number' then information end) as phonenumber,
       max(case when infotype like '%comment%' then information end) as comment
from tablename
group by receiptid