SQL Server : Stuff function roll up multiple rows groping by line number

Question

I'm working with these two tables, cl_line_rem & Remark_code. They are joined on the Remark_code_id column.

select Claim_id, Linenum, Remark_code_ID
from claim_line_remark 
where claim_id = 13639035

Output:

Claim_id      Linenum   Remark_code_ID
---------------------------------------
13639035    1   697
13639035    1   739
13639035    1   1322
13639035    2   697
13639035    2   739
13639035    2   1322
13639035    3   697
13639035    3   739
13639035    3   1322
13639035    4   697
13639035    4   739
13639035    4   1322
13639035    5   697
13639035    5   739
13639035    5   1322
13639035    6   697
13639035    6   739
13639035    6   1322

select clr.claim_id, clr.linenum, clr.remark_code_id, rc.name
from claim_line_remark clr
inner join Remark_Code rc on clr.Remark_Code_ID = rc.Remark_Code_ID
where claim_id = 13639035

Output:

claim_id    linenum   Remark_code_id    Name
------------------------------------------------
13639035    1   697       HAWAII FFS SCHEDULE
13639035    1   739       DO NOT BALANCE BILL.  
13639035    1   1322          SUPPRESS ALL EOB
13639035    2   697       HAWAII FFS SCHEDULE
13639035    2   739       DO NOT BALANCE BILL.  
13639035    2   1322          SUPPRESS ALL EOB
13639035    3   697       HAWAII FFS SCHEDULE
13639035    3   739       DO NOT BALANCE BILL. 
13639035    3   1322          SUPPRESS ALL EOB
13639035    4   697       HAWAII FFS SCHEDULE
13639035    4   739       DO NOT BALANCE BILL.  
13639035    4   1322          SUPPRESS ALL EOB
13639035    5   697       HAWAII FFS SCHEDULE
13639035    5   739       DO NOT BALANCE BILL. 
13639035    5   1322          SUPPRESS ALL EOB
13639035    6   697       HAWAII FFS SCHEDULE
13639035    6   739       DO NOT BALANCE BILL. 
13639035    6   1322          SUPPRESS ALL EOB

I've tried using this code with Stuff function but doesnt seem to be working exactly:

  select 
      clr.Claim_ID, clr.Linenum,
      stuff((select '; ' + rc.Name
             from Remark_Code rc
             where rc.Remark_Code_ID = clr.Remark_Code_ID
             for xml path('')), 1, 1, '')  [Remarks]
  from 
      Claim_Line_Remark clr  
  where 
      claim_id in (13639035)

What I'm looking for is this,

Claim_id     Linenum   Name
------------------------------------------------------------------------------
13639035    1       HAWAII FFS SCHEDULE,DO NOT BALANCE BILL,SUPPRESS ALL..
13639035    2       HAWAII FFS SCHEDULE,DO NOT BALANCE BILL,SUPPRESS ALL..
13639035    3       HAWAII FFS SCHEDULE,DO NOT BALANCE BILL,SUPPRESS ALL..
13639035    4       HAWAII FFS SCHEDULE,DO NOT BALANCE BILL,SUPPRESS ALL..
13639035    5       HAWAII FFS SCHEDULE,DO NOT BALANCE BILL,SUPPRESS ALL..
13639035    6       HAWAII FFS SCHEDULE,DO NOT BALANCE BILL,SUPPRESS ALL..

What I'm getting with the current code is this,

13639035    1    HAWAII FFS SCHEDULE
13639035    1    DO NOT BALANCE BILL.  
13639035    1    SUPPRESS ALL EOB
13639035    2    HAWAII FFS SCHEDULE
13639035    2    DO NOT BALANCE BILL.  
13639035    2    SUPPRESS ALL EOB
13639035    3    HAWAII FFS SCHEDULE
13639035    3    DO NOT BALANCE BILL.  
13639035    3    SUPPRESS ALL EOB
13639035    4    HAWAII FFS SCHEDULE
13639035    4    DO NOT BALANCE BILL. 
13639035    4    SUPPRESS ALL EOB
13639035    5    HAWAII FFS SCHEDULE
13639035    5    DO NOT BALANCE BILL. 
13639035    5    SUPPRESS ALL EOB
13639035    6    HAWAII FFS SCHEDULE
13639035    6    DO NOT BALANCE BILL. 
13639035    6    SUPPRESS ALL EOB

Possible duplicate of [How to concatenate text from multiple rows into a single text string in SQL server?](https://stackoverflow.com/questions/194852/how-to-concatenate-text-from-multiple-rows-into-a-single-text-string-in-sql-serv) and [Comma separated results in SQL](https://stackoverflow.com/questions/18870326/comma-separated-results-in-sql)? — Thom A, Jul 10 '19 at 15:28

score 1 · Accepted Answer · answered Jul 10 '19 at 16:08

Try this below code-

WITH CTE
AS
(
    SELECT clr.claim_id, 
           clr.linenum, 
           clr.remark_code_id, 
           rc.name
    FROM claim_line_remark clr
         INNER JOIN Remark_Code rc ON clr.Remark_Code_ID = rc.Remark_Code_ID
    WHERE claim_id = 13639035
)

SELECT B.claim_id,B.linenum,
STUFF((
    SELECT DISTINCT  ',' + A.Name 
    FROM CTE A 
    WHERE A.claim_id = B.claim_id AND A.linenum = B.linenum  
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, ''
) Name
FROM CTE B
group by B.claim_id,B.linenum

Output is-

claim_id    linenum Name
13639035    1       DO NOT BALANCE BILL.,HAWAII FFS SCHEDULE,SUPPRESS ALL EOB
13639035    2       DO NOT BALANCE BILL.,HAWAII FFS SCHEDULE,SUPPRESS ALL EOB
13639035    3       DO NOT BALANCE BILL.,HAWAII FFS SCHEDULE,SUPPRESS ALL EOB
13639035    4       DO NOT BALANCE BILL.,HAWAII FFS SCHEDULE,SUPPRESS ALL EOB
13639035    5       DO NOT BALANCE BILL.,HAWAII FFS SCHEDULE,SUPPRESS ALL EOB
13639035    6       DO NOT BALANCE BILL.,HAWAII FFS SCHEDULE,SUPPRESS ALL EOB

SQL Server : Stuff function roll up multiple rows groping by line number

1 Answers1