How do I combine multiple rows into one column in SQL?

Question

My code looks like below:

select distinct
    HSP_ACCOUNT_ID
    ,TOT_ACCT_BAL
    ,DISCH_DATE_TIME
    ,UB_REV_CODE_ID
    ,PROC_CODE
    ,DATEDIFF(dd,act.DISCH_DATE_TIME,GETDATE()) as "Aging"

from HSP_ACCOUNT 

where 
    SERV_AREA_ID = 10
    and ACCT_BILLSTS_HA_C = '3' --Billed
    and DISCH_DATE_TIME >= '20171024'
    and TOT_ACCT_BAL is not null
    and TOT_ACCT_BAL <> 0

With results like below:

HSP_ACCOUNT_ID  TOT_ACCT_BAL    DISCH_DATE_TIME UB_REV_CODE_ID  PROC_CODE Aging
117244          830.03          10/24/2017  320         32074230  2
117244          830.03          10/24/2017  440         44008996  2
117244          830.03          10/24/2017  440         44008997  2
117244          830.03          10/24/2017  440         44008998  2
117244          830.03          10/24/2017  444         44492611  2

How can I get the all the different UB_REV_CODE_ID and PROC_CODE to populate on one row for each Account ID?

Possible duplicate of [Can I concatenate multiple MySQL rows into one field?](https://stackoverflow.com/questions/276927/can-i-concatenate-multiple-mysql-rows-into-one-field) — Ken White, Oct 26 '17 at 18:05

score 0 · Answer 1 · answered Oct 26 '17 at 18:09

Group_concat() will achieve the required task.

select distinct
    HSP_ACCOUNT_ID
    ,TOT_ACCT_BAL
    ,DISCH_DATE_TIME
    ,Group_concat(Distinct UB_REV_CODE_ID separator ' ')
    ,Group_concat(Distinct PROC_CODE separator ' ')
    ,DATEDIFF(dd,act.DISCH_DATE_TIME,GETDATE()) as "Aging"

from HSP_ACCOUNT 

where 
    SERV_AREA_ID = 10
    and ACCT_BILLSTS_HA_C = '3' --Billed
    and DISCH_DATE_TIME >= '20171024'
    and TOT_ACCT_BAL is not null
    and TOT_ACCT_BAL <> 0
Group by HSP_ACCOUNT_ID;

Note: You should only use above solution if all other columns (except 2 codes) have same values in each row for particular Account Id.

Hope it helps!

How do I combine multiple rows into one column in SQL?

1 Answers1