Combine Multiple Rows into one row on a single tab

Question

I have a table that contains multiple Insurance policies for each client that contains

Order (Primary, Secondary etc. up to 9 possible Insurance policies)

I need one row per client containing all the client's insurance policies in order of importance.

for example:

  Client, Order, Company, Number, Start,    End, Co-Pay, Active?
  1       1      BCBS     12345   1/1/2019       $10     Active
  1       2      Medicare X21Y52  2/1/2018               Active
  1       6      Self-Pay    
  2       6      Medicare X21Y52  2/1/2018               Active
  2       8      Self-Pay    

I need a single row for each Client with the data for each Active Policy

1,1,BCBS,12345,1/1/2019,,$10,Active,2,Medicare,X21Y52,2/1/2018,,,Active,6,Self-Pay,,,,,  
2,6,Medicare,x22y22,7/1/2002,,,Active,8,Self-Pay,,,,,,    

How can I code to get the desired result.

There is a maximum of 9 policy references

Only want Active policies

Answer 1

As you didn't post your schema, i just make some assumption on the data type. Note that you need to convert to string before concatenation

select  t.Client, stuff(csv, 1, 1, '')
from    (
            select  distinct Client
            from    a_table t
        ) t
        cross apply
        (
            select  ',' + coalesce(x.Company, '') +
                    ',' + coalesce(x.Number, '') +
                    ',' + coalesce(convert(varchar(10), [Start], 121), '') +
                    ',' + . . . 
            from    a_table x
            where   x.Client    = t.Client
            and     x.Active    = 'Active'
            order by [Order]
            for xml path ('')
        ) c (csv)