将多行连接成一行 [英] Concatenate multiple rows into one row
问题描述
我刚刚在 SQL Server 上学习 SQL.我需要将多行连接成一列.我寻找了一些示例,但没有找到可以满足我需求的示例.
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.
推荐答案
不幸的是 SQL Server 没有一个简单的函数来生成逗号分隔的列表,你必须实现 FOR XML PATH
才能得到清单.
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.
有几种方法可以做到这一点,您可以使用STUFF
和FOR XML PATH
:
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;
或者你可以使用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 with Demo.这些都产生了结果:
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 |
这篇关于将多行连接成一行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!