SQL Query to merge the contents from multiple rows using another column

Question

0.00/5 (No votes)

See more:

In the result set you can see we have 2 claimkeys , i want to replace 2nd row primarypayment column value (ie 116 ) with 1st row primarypayment column so i need result in one row.

SQL

SELECT    distinct ecpc.ecpclaim_key AS claim_key
    ,ecpc.ecpclaim_patient_last_name AS patient_last_name
    ,ecpc.ecpclaim_patient_first_name AS patient_first_name
    ,(SELECT MIN(ecpservice_date_from) FROM tbl_ecpserviceline WHERE ecpclaim_key = ecpc.ecpclaim_key) AS DOS
    ,ecpc.ecpclaim_carrier_id AS payer_id
    ,ecpc.eCPClaim_carrier_name AS payer_name
    ,(CASE WHEN eCPLedger_Master_Transaction_Type IN (1001) THEN ecpledger_master_transaction_amount END )  AS Charges
    ,(CASE WHEN eCPLedger_Master_Transaction_Type IN (1004,1007) THEN (SELECT DISTINCT ARRAY_TO_STRING(ARRAY_AGG(ecpledger_master_transaction_payment ), '') FROM  tbl_ecpledger_master WHERE eCPLedger_Master_Transaction_Type IN (1004,1007) and ecpc.ecpclaim_key=ecpclaim_key )
     END ) AS PrimayPayment
    ,LM.ecpledger_master_claim_balance

    FROM
        tbl_ecpclaim ecpc
        inner JOIN tbl_ecpledger_master LM  ON ecpc.ecpclaim_key=LM.ecpclaim_key


    WHERE ecpc.ecpclaim_insurancetype='P'

    GROUP BY ecpc.ecpclaim_key,lm.ecpledger_master_claim_balance,lm.ecpledger_master_transaction_type,lm.ecpledger_master_transaction_amount limit 10

Result for above query
Claim P_l P_F DOS Payer_i Payer_Name Charges PrimaryPayment Balance
18801 Lyons Wendy 2014 23071 Alliance 116 NULL 0
18801 Lyons Wendy 2014 23071 Alliance 116 NULL

Excepted out put

Claim P_l P_F DOS Payer_id Payer_Name Charges PrimaryPayment Balance
18801 Lyons Wendy 2014 23071 Alliance 116 116 0

Posted 9-Dec-14 17:00pm

Member 10256268

Updated 10-Dec-14 20:01pm

Add a Solution

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sinisa Hajnal · Answer 1 · 2014-12-09T20:44:00

Just add ISNULL to your balance and it will create two zeros which will be eliminated by DISTINCT. I hope this helps (change in bold)

SQL

SELECT    distinct ecpc.ecpclaim_key AS claim_key
    ,ecpc.ecpclaim_patient_last_name AS patient_last_name
    ,ecpc.ecpclaim_patient_first_name AS patient_first_name
    ,(SELECT MIN(ecpservice_date_from) FROM tbl_ecpserviceline WHERE ecpclaim_key = ecpc.ecpclaim_key) AS DOS
    ,ecpc.ecpclaim_carrier_id AS payer_id
    ,ecpc.eCPClaim_carrier_name AS payer_name
    ,(CASE WHEN eCPLedger_Master_Transaction_Type IN (1001) THEN ecpledger_master_transaction_amount END )  AS Charges
    ,(CASE WHEN eCPLedger_Master_Transaction_Type IN (1004,1007) THEN (SELECT DISTINCT ARRAY_TO_STRING(ARRAY_AGG(ecpledger_master_transaction_payment ), '') FROM  tbl_ecpledger_master WHERE eCPLedger_Master_Transaction_Type IN (1004,1007) and ecpc.ecpclaim_key=ecpclaim_key )
     END ) AS PrimayPayment
    ,COALESCE(LM.ecpledger_master_claim_balance, 0.00) as balance

    FROM
        tbl_ecpclaim ecpc
        inner JOIN tbl_ecpledger_master LM  ON ecpc.ecpclaim_key=LM.ecpclaim_key


    WHERE ecpc.ecpclaim_insurancetype='P'

    GROUP BY ecpc.ecpclaim_key,lm.ecpledger_master_claim_balance,lm.ecpledger_master_transaction_type,lm.ecpledger_master_transaction_amount limit 10

SQL Query to merge the contents from multiple rows using another column

1 solution

Solution 1

Add your solution here

Preview 0

SQL Query to merge the contents from multiple rows using another column

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us