如何选择除最后一列之外的所有列，以及以逗号分隔值的最后一列

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

我有一个具有以下结构的表。它有8列,其中1st 7有时包含重复,最后一列可以有不同的值(尽管没有唯一值)。

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

如何为前7列选择distinct,然后将最后一列显示为逗号分隔值。

So the last column should look like following,

所以最后一列应如下所示,

1 个解决方案

#1

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

在子查询中选择不同的7值,然后执行XML STUFF以合并值

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

#1