CODEWITHSUNDEEP
sqlsql-servert-sql
Lalalala9999
Lalalala9999

Reputation: 149

How to merge 2 rows into 1

The code I used was: 

SELECT
    AccountFK,
    CASE WHEN B.ContractType = 'Telecoms' Then B.TermFeeMethod END AS TelecomsTermMethod,
    CASE WHEN B.ContractType = 'Broadband' Then B.TermFeeMethod END AS BroadbandTermMethod,
    CASE WHEN B.ContractType = 'Mobile' Then B.TermFeeMethod END AS MobileTermMethod,
    CASE WHEN B.ContractType = 'Water' Then B.TermFeeMethod END AS WaterTermMethod,
    ROW_NUMBER () OVER (PARTITION BY B.AccountFK ,B.TermFeeMethod ORDER BY A.CreatedDate DESC) AS RowNo
FROM 
    CRM.Contract.TermFeeTransaction AS A
    INNER JOIN ContractSnapshot AS B ON A.ContractFK = B.ContractID
WHERE 
CAST(b.CreatedDate as date) between '20180101' and '20190812'
AND B.TermFeeMethod IS NOT NULL 
AND ContractType IN
('Telecoms',
 'Broadband',
 'Mobile',
 'Water')
 and AccountFK = '12345'

The output I'm looking for would be only one row, however, I'm getting two rows instead. Does anybody know how to merge them into one?

enter image description here

Upvotes: 0

Views: 73

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269445

Use conditional aggregation:

SELECT AccountFK,
       MAX(CASE WHEN B.ContractType = 'Telecoms' Then B.TermFeeMethod END) AS TelecomsTermMethod,
       MAX(CASE WHEN B.ContractType = 'Broadband' Then B.TermFeeMethod END) AS BroadbandTermMethod,
       MAX(CASE WHEN B.ContractType = 'Mobile' Then B.TermFeeMethod END) AS MobileTermMethod,
       MAX(CASE WHEN B.ContractType = 'Water' Then B.TermFeeMethod END) AS WaterTermMethod,
    ROW_NUMBER () OVER (PARTITION BY B.AccountFK ,B.TermFeeMethod ORDER BY A.CreatedDate DESC) AS RowNo
FROM CRM.Contract.TermFeeTransaction A JOIN
     ContractSnapshot B
     ON A.ContractFK = B.ContractID
WHERE CAST(b.CreatedDate as date) between '20180101' and '20190812' AND
      B.TermFeeMethod IS NOT NULL AND
      ContractType IN ('Telecoms', 'Broadband', 'Mobile', 'Water') AND
      AccountFK = '12345'
GROUP BY AccountFK;

Note: A and B are really bad choices for table aliases. They are just arbitrary letters. You should use tft and cs instead -- these are abbreviations for the table names.

Upvotes: 4

Related Questions