Concatenate multiple rows into one row

问题:

I am just now learning SQL on SQL Server. I need to concatenate multiple rows into one column. I have looked for examples, but do not find one that I can use to suit my needs.

Country     ProjectTA   Complexity TID  Sites   Inits   Name
United States A8022 Obesity Low  4692   69  JT  AD
United States A8022 Obesity Low  4692   69  jpni    CBM Budget
United States A8022 Obesity Low  4692   69  PIHR    AD
United States A8022 Obesity Low  4692   69  jpni    CBM Budget
United States A8022 Obesity Low  4692   69  hale    ePublishing Group
United States S8033 CNS Medium   5423   69  ShyP    CBM Payment
United States S8033 CNS Medium   5423   69  dedu    ePublishing Group
United States S8033 CNS Low  5423   69  AHrp    ePublishing Group

I want to concatenate rows by Name and have Inits listed with ','

United States A8022 Obesity Low 4692    69 JT,PIHR  AD
United States A8022 Obesity Low 4692    69 jpni, PIHR   CBM Budget
United States A8022 Obesity Low 4692    69 hale          ePublishing Group
United States S8033 CNS Medium  5423    69 ShyP          CBM Payment
United States S8033 CNS Medium  5423    69 dedu, Ahrp   ePublishing Group

Any help would be appreciated. Thanks so much.

回答1:

Unfortunately SQL Server does not have an easy function to generate a comma separated list, you will have to implement FOR XML PATH to get the list.

There are a few ways to do this, you can use STUFF and FOR XML PATH:

select distinct t1.country,
  t1.ProjectTA, 
  t1.Complexity,
  t1.TID,
  t1.Sites,
  STUFF(
         (SELECT ', ' + t2.Inits
          FROM yt t2
          where t1.Country = t2.Country
            and t1.ProjectTA = t2.ProjectTA
            and t1.TID = t2.TID
            and t1.Sites = t2.Sites
            and t1.name = t2.name
          FOR XML PATH (''))
          , 1, 1, '')  AS inits,
  t1.name
from yt t1;

See SQL Fiddle with demo

Or you can use CROSS APPLY and FOR XML PATH:

select distinct t1.country,
  t1.ProjectTA, 
  t1.Complexity,
  t1.TID,
  t1.Sites, 
  left(t2.inits, len(t2.inits)-1) inits,
  t1.name
from yt t1
cross apply
(
  select t2.Inits + ', '
  from yt t2
  where t1.Country = t2.Country
    and t1.ProjectTA = t2.ProjectTA
    and t1.TID = t2.TID
    and t1.Sites = t2.Sites
    and t1.name = t2.name
  FOR XML PATH('')
) t2 (inits);

See SQL Fiddle with Demo. These both yield the result:

|       COUNTRY | PROJECTTA |  COMPLEXITY |  TID | SITES |      INITS |              NAME |
-------------------------------------------------------------------------------------------
| United States |     A8022 | Obesity Low | 4692 |    69 |       hale | ePublishing Group |
| United States |     A8022 | Obesity Low | 4692 |    69 | jpni, jpni |        CBM Budget |
| United States |     A8022 | Obesity Low | 4692 |    69 |   JT, PIHR |                AD |
| United States |     S8033 |     CNS Low | 5423 |    69 | dedu, AHrp | ePublishing Group |
| United States |     S8033 |  CNS Medium | 5423 |    69 | dedu, AHrp | ePublishing Group |
| United States |     S8033 |  CNS Medium | 5423 |    69 |       ShyP |       CBM Payment |

回答2:

To do this in SQL Server, you need to do concatenate the strings together as an aggregatino function. Unfortunately, SQL Server does not have group_concat() or listagg() or some related function. Here is the method in SQL Server:

select Country, ProjectTA, Complexity, TID,
       stuff((select ', '+Inits
              from t t2
              where t2.Name = t.Name and
                    t2.country = t.country and
                    t2.ProjectTA = t.ProjectTA and
                    t2.Tid = t.Tid
              for xml path(''), type
             ).value('.','nvarchar(max)'), 1, 2, ''
            ) as InitsList,
       Name
from t
group by country, ProjectTA, Complexity, TID, name;

It looks like you are grouping by more than name, so I included all of the fields on the row apart from inits.