CODEWITHSUNDEEP
sqlsql-server
mohamed abd elnabi
mohamed abd elnabi

Reputation: 23

SELECTING from same field in 2 column

I want to create a report that appears from the same field in two columns example : 

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

Result : 

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

I use this code but the result is not same I need:

enter image description here

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 ),())

enter image description here

Upvotes: 0

Views: 102

Answers (2)

ashan
ashan

Reputation: 54

select * from
(
SELECT [receipt id]
  ,[infotype]
  ,[information]
FROM [test].[dbo].[report]) as x
pivot
(
max([information])
for [infotype] in([phone],[comment]) )as pvt

Upvotes: 0

Fahmi
Fahmi

Reputation: 37473

You can use conditional aggregation

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

Upvotes: 7

Related Questions