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).
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,
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"
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