How to have select distinct of all but last column , and last column as comma separated values

I have a table with following structure. It has 8 columns where 1st 7 some time contain duplicate and last one can have different values (no unique though).

enter image description here

How do I select distinct for 1st 7 columns and then show the last column as comma separated values.

So the last column should look like following,

enter image description here

1 answer

  • answered 2018-02-13 01:06 John Cappelletti

    Select the distinct 7 values in a sub-query and then perform the XML STUFF to consolidate the values

    Please note the "--Add your other fields here"

    Example

    Select A.*
          ,CollectionDate  = Stuff((Select Distinct ',' +cast(CollectionDate as varchar(max))
                                     From  YourTable 
                                     Where Quantity=A.Quantity 
                                       and Protein =A.Protein
                                       and Carb    =A.Carb
                                       -- Add your other fields here
                                     For XML Path ('')),1,1,'') 
     From (Select Distinct 
                  Quantity
                 ,Protein
                 ,Carb
                 -- Add your other fields here
            From  YourTable ) A