Combining Claim lines For Unique Values

Question

I'm working with medical claims where a single claim number can have up to 75 lines. Each claim has a code that predicates how the claim is handled and what I need to do for both stats and efficient case processing is combine all these lines together and grab just the unique values- some claims can have 20-30 lines with the exact same codes and i would obviously just need to know what those two codes are, not each individual line. I feel like this is a simple solution but it's just escaping my grasp.

Original Data:

Claim Number    EX1 Ex2 Ex3 Ex4 Ex5 Ex6
123456789123    LP  DU  1O            
123456789123    LP  DU  1O            
123456789123    LP  DU  1O            
123456789123    LP  DU  1O            
123456789123    LP  DU  1O            
123456789123    LP  DU  1O            
123456789123    LP  DU  1O            
123456789123    LP  DU  1O            
123456789123    LP  DU  1O            
123456789123    LP  DU  1O            
123456789123    LP  DU  1O            
123456789123    LP  DU  1O            
123456789123    LP  DU  1O            
123456789123    LP  DU  1O            
123456789123    LP  IK  D3  AP  1O    
123456789123    LP  DU  1O            
123456789123    LP  IK  D3  AP  1O    
123456789123    LP  DU  1O            
123456789123    LP  DU  1O            
123456789123    LP  DU  1O            
123456789123    LP  IK  D3  AP  1O    
123456789123    LP  DU  1O            
123456789123    LP  DU  1O            
123456789123    LP  DU  1O            
123456789123    LP  DU  1O            
123456789123    LP  DU  1O            
123456789123    LP  DU  1O            
123456789123    LP  DU  1O            
123456789123    LP  DU  1O            
123456789123    LP  DU  1O            
123456789123    LP  DU  1O            
123456789123    LP  DU  1O            
123456789123    LP  DU  1O            
123456789123    LP  IK  D3  AP  1O    
123456789123    LP  DU  1O            
123456789123    LP  DU  1O            
123456789123    LP  DU  1O            
123456789123    LP  DU  1O            
123456789123    LP  DU  1O            
123456789123    LP  DU  1O            
123456789123    LP  DU  1O            
123456789123    LP  DU  1O            
123456789123    LP  DU  1O            
123456789123    LP  DU  1O            
123456789123    LP  DU  1O            
123456789123    LP  DU  1O            
123456789123    LP  DU  1O            
123456789123    LP  DU  1O            
123456789123    LP  DU  1O            
123456789123    LP  DU  1O            
123456789123    LP  DU  1O            
123456789123    LP  DU  1O            
123456789123    LP  DU  1O            
123456789123    LP  IK  D3  AP  1O    
123456789123    LP  DU  1O            
123456789123    LP  DU  1O            
123456789123    LP  DU  1O            
123456789123    LP  DU  1O            
123456789123    LP  DU  1O            
123456789123    LP  DU  1O            
123456789123    LP  DU  1O            
123456789123    LP  IK  D3  AP  1O    
123456789123    LP  DU  1O            
123456789123    LP  DP  AP  1O        
123456789123    LP  DU  1O            
123456789123    LP  DU  1O            
123456789123    LP  DU  1O            
123456789123    LP  DU  1O            
123456789123    LP  DU  1O

Select Distinct:

Claim Number    EX1 Ex2 Ex3 Ex4 Ex5 Ex6
123456789123    LP  DP  AP  1O        
123456789123    LP  DU  1O            
123456789123    LP  IK  D3  AP  1O

But what I'm looking for would be something like

Claim Number    Codes
123456789123    LP,DP,AP,1O,DU,IK,D3

The only solution I can think of is to create a big text string by adding a row column, putting them in their own table/subquery, and adding those together but i'd still get all the EX codes duplicated. Plus I don't know how many distinct lines a claim might have so the code would be repetitious and long for that many. I would be able to search for specific claims if i just made the codes into a long text string but i feel like there has t0 be a more elegant solution.

I've tried searching for this question but maybe I don't know exactly what I'm looking for, so I'm sorry if this is a repeat of something that's been asked already. I should also say any sql i've learned has largely been based on what I've needed to do so if this question is basic I apologize >.>

Is this for dummy purposes or are you designing an actual business solution? If it is the latter, there are plenty of tools out there that parse claims files which should be getting communicated via 837 ETL formatting. — dfundako, Jun 01 '18 at 15:17
Designing a solution. We have Tiered analysts and need to prioritize our claims based on who can handle them. Truthfully our support from our vendor hasn't been amazing so far. We're kinda having to come up with a lot of our own solutions. — Chris P, Jun 01 '18 at 15:21
Good luck. Healthcare is a messy, messy beast and claims(professional, institutional, and dental) are especially bad. — dfundako, Jun 01 '18 at 15:24
I think what you'll need to do is UNPIVOT everything to rows (https://stackoverflow.com/questions/18026236/sql-server-columns-to-rows?utm_medium=organic&utm_source=google_rich_qa&utm_campaign=google_rich_qa) , then SELECT DISTINCT ignoring the column of Ex indicators, then do a row concatenation (https://stackoverflow.com/questions/194852/how-to-concatenate-text-from-multiple-rows-into-a-single-text-string-in-sql-serv?utm_medium=organic&utm_source=google_rich_qa&utm_campaign=google_rich_qa) — Error_2646, Jun 01 '18 at 15:39

score 2 · Answer 1 · answered Jun 01 '18 at 15:53

If I understand correctly, you can unpivot and then combine the codes:

with oc as (
      select distinct claim, ex
      from original o cross apply
           (values (ex1), (ex2), (ex3), (ex4), (ex5)) v(ex)
     )
select o.claim,
       stuff( (select ',' + ex
               from oc
               where oc.claim = o.claim
               for xml path ('')
              ), 1, 1, ''
            ) as codes
from (select distinct claim
      from original o
     ) o;

score 0 · Answer 2 · answered Jun 01 '18 at 15:54

SQL Server 2012 version:

with CTE as (
    select distinct [Claim_Number], [EX1] ex from TestTable
    union select [Claim_Number], [EX2] ex from TestTable
    union select [Claim_Number], [EX3] ex from TestTable
    union select [Claim_Number], [EX4] ex from TestTable
    union select [Claim_Number], [EX5] ex from TestTable
    union select [Claim_Number], [EX6] ex from TestTable
)
select [Claim_Number],
  Codes = STUFF((SELECT  ','+cast([ex] AS VARCHAR ) FROM  CTE t WHERE  C.[Claim_Number]  = t.[Claim_Number]  FOR XML PATH('')),1,1,'') 
from CTE C
group by Claim_Number

SQL Fiddle DEMO LINK

PS. you can do it in sql server 2017

try it "use uion and Group by Claim_Number + STRING_AGG"

select [Claim_Number],STRING_AGG ( ex, ',')
from (
    select [Claim_Number], [EX1] ex from TestTable
    union select [Claim_Number], [EX2] ex from TestTable
    union select [Claim_Number], [EX3] ex from TestTable
    union select [Claim_Number], [EX4] ex from TestTable
    union select [Claim_Number], [EX5] ex from TestTable
    union select [Claim_Number], [EX6] ex from TestTable
) T
group by Claim_Number

SQL Fiddle DEMO LINK

Combining Claim lines For Unique Values

2 Answers2

SQL Server 2012 version: