SQL Query使用另一列合并多行中的内容 [英] SQL Query to merge the contents from multiple rows using another column

问题描述

在结果集中你可以看到我们有2个claimkeys，我想用第一行primarypayment列替换第二行primarypayment列值（即116），所以我需要一行结果。

< br $>

  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 ）那么 ecpledger_master_transaction_amount  END ）  AS 收取
，（ CASE   WHEN  eCPLedger_Master_Transaction_Type  IN （ 1004 ， 1007  ）那么（ SELECT   DISTINCT  ARRAY_TO_STRING（ARRAY_AGG （ecpledger_master_transaction_payment），' '） FROM  tbl_ecpledger_master  WHERE  eCPLedger_Master_Transaction_Type  IN （ 1004 ， 1007 ）和 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  

以上查询结果

索赔P_l P_F DOS Payer_i Payer_Name收费PrimaryPayment平衡

18801 Lyons Wendy 2014 23071联盟116 NULL 0

18801 Lyons Wendy 2014 23071联盟116 NULL



优惠出局



索赔P_l P_F DOS Payer_id Payer_Name收费PrimaryPayment余额

18801 Lyons Wendy 2014 23071联盟116 116 0

解决方案

只需将ISNULL添加到您的余额中，它将创建两个零，这些零将被DISTINCT消除。我希望这有帮助（改变粗体）

  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 ）那么 ecpledger_master_transaction_amount  END ） AS 收取
，（ CASE   WHEN  eCPLedger_Master_Transaction_Type  IN （ 1004 ， 1007 ）< span class =code-keyword> THEN （ SELECT   DISTINCT  ARRAY_TO_STRING（ARRAY_AGG（ecpledger_master_transaction_payment） ），' '） FROM  tbl_ecpledger_master < span class =code-keyword> WHERE  eCPLedger_Master_Transaction_Type  IN （ 1004 ， 1007 ）和 ecpc.ecpclaim_key = e cpclaim_key）
  END ） AS  PrimayPayment 
 ， < span class =code-keyword> 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  

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.

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

解决方案

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)

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使用另一列合并多行中的内容的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！