显示多个值一行

Question

我有以下两个表

TableA   Table B 

id    bid bname  btitle 
----    ------------------------------ 
1    1 john  titlejohn 
2    1 william titlewilliam 
3    1 george titlegeorge 
       2 bill  titlebill 
       3 kyle  titlekyle 
       3 seb  titleseb 

我需要在SQL Server查询，显示下面的输出：

id name      title 
1 john,william,george  titlejohn,titlewilliam,titlegeorgw 
2 bill      titlebill 
3 kyle,seb     titlekyle,titleseb 

请帮助。

Answer 1

这里有一个解决方案。它只处理bname，但您可以扩展它以处理btitle。给定键的列值并不是SQL中的自然事物，因此您需要一个技巧来循环访问使用相同键提取每行的表。诀窍是创建一个内存表与标识列（n说），每个插入自动增量。你可以循环，然后选择n = 1，然后n = 2等来构建字符串。

create function tbl_join_name(@id int) 
returns varchar(max) 
as 
begin 
    declare @tbl table (n int identity(1,1), name varchar(max), title varchar(max)) 
    insert @tbl(name, title) 
    select bname, btitle from TableB where bid = @id 

    declare @n int = 1, @name varchar(max) = '', @count int = (select count(*) from @tbl) 
    while @n <= @count begin 
     set @name = @name + (case @name when '' then '' else ',' end) + (select name from @tbl where n = @n) 
     set @n = @n + 1 
    end 
    return @name 
end 
go 

select id, tbl_join_name(id) as bname --, tbl_join_title(id) as btitle 
from TableA 

虽然这不是很有效率。使用Sql Server 2008 R2进行测试。

Answer 2

select id, name = stuff(n.name, 1, 1, ''), title = stuff(t.title, 1, 1, '') 
from TableA a 
    outer apply 
    (
     select ',' + bname 
     from TableB x 
     where x.bid = a.id 
     for xml path('') 
    ) n (name) 
    outer apply 
    (
     select ',' + btitle 
     from TableB x 
     where x.bid = a.id 
     for xml path('') 
    ) t (title) 

Answer 3

的另一种方法：

SELECT A.id, 
     STUFF((SELECT ','+bname 
     FROM TableB B 
     WHERE B.bid = A.id 
     FOR XML PATH('')),1,1,'') as name, 
     STUFF((SELECT ','+btitle 
     FROM TableB B 
     WHERE B.bid = A.id 
     FOR XML PATH('')),1,1,'') as title 
FROM TableA A 

输出：

id name    title 
1 john,william,george titlejohn,titlewilliam,titlegeorge 
2 bill    titlebill 
3 kyle,seb   titlekyle,titleseb