代码之家 › 专栏 › 技术社区 › Mitesh Jain

SQL Server 2014中表中需要逗号分隔值[重复]

sql-server-2012 sql-server

Mitesh Jain · 技术社区 · 7 年前

这个问题已经有了答案:

get a comma delimited string from rows [duplicate] 2个答案

我有一张这样的桌子。

Create table #temp
(
    id int,
    firstname varchar(50),
    lastname varchar(50)
)

insert into #temp (id, firstname, lastname)
select 1,'mit','jain'

insert into #temp (id, firstname, lastname)
select 1,'mit','jain1'

insert into #temp (id, firstname, lastname)
select 1,'mit','jain2'

insert into #temp (id, firstname, lastname)
select 2,'mit','jain3'

insert into #temp (id, firstname, lastname)
select 2,'mit','jain4'

insert into #temp (id, firstname, lastname)
select 1,'mit','jain5'

insert into #temp (id, firstname, lastname)
select 1,'mit','jain6'

我想这张桌子如下图所示

id  firstname  lastname
----------------------------------------------
 1   mit        jain,jain1,jain2,jain5,jain6 
 2   mit        jain2,jain4

我试过下面的查询

select  
    id, firstname,
    substring((Select ', '+tc1.lastname AS [text()]
               From #temp tc1
               Inner Join #temp c1 On c1.id = tc1.id
               Where tc1.firstname = c1.firstname
               Order BY tc1.lastname
               For Xml Path('')), 2, 1000) 'LastName1' 
from #temp 
group by id, firstname

但它不起作用。请帮帮我

1 回复 | 直到 7 年前

Larnu 7 年前

你是这条路的一部分。传统的方法是 STUFF :

SELECT t.id, t.firstname,
       STUFF((SELECT ', ' + sq.lastname
              FROM #temp sq
              WHERE sq.id = t.id
                AND sq.firstname = t.firstname
              ORDER BY sq.lastname
              FOR XML PATH('')),1,1,'') AS lastname
FROM #temp t
GROUP BY t.id, t.firstname;

关于如何做到这一点,已经有很多答案了,但是你已经表现出了努力。:)

推荐文章