将多行连接为一行

Question

我现在正在学习SQL Server上的SQL技术，需要将多行数据合并为一列。我已经查找了一些示例，但没有找到适合我需求的。

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

我想要按照姓名合并行，并用“，”列出缩写。

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

非常感谢您的帮助。任何帮助都将不胜感激。谢谢。

- Mona

---

我不明白你什么时候需要在逗号后面加空格，什么时候不需要。有什么区别吗？ - RichardTheKiwi

Answer 1

不幸的是，SQL Server没有一个简单的函数来生成逗号分隔的列表，你需要实现FOR XML PATH来获取列表。

有几种方法可以做到这一点，你可以使用STUFF和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;

请查看带有演示的SQL Fiddle

或者您可以使用CROSS APPLY和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);

请查看带演示的SQL Fiddle。两者都可以产生以下结果：

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

- Taryn

Answer 2

在SQL Server中，要做到这一点，您需要将字符串连接在一起作为聚合函数。不幸的是，SQL Server没有group_concat()或listagg()或某些相关函数。以下是在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;

看起来您正在按照不止名称进行分组，因此我在行中包含了除inits之外的所有字段。

- Gordon Linoff

---

请尽量检查您的工作并使用FOR XML..TYPE表单作为一般规则。对于不聪明到无法进行SQL Server连接的新手来说，他们可能会很难理解为什么会在您的代码中得到Msg 512, Level 16, State 1, Line 1: Subquery returned more than 1 value.。请注意，对于', '（2个字符前缀），您需要使用STUFF去掉2个字符。 - RichardTheKiwi

@RichardTheKiwi...非常感谢。我离开电脑一个小时后意识到查询中犯了这两个错误（遗漏了for xml path和stuff()上的2）。 - Gordon Linoff