将多个数据行连接成一行 [英] Concatenate multiple data rows into one row
本文介绍了将多个数据行连接成一行的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有三个表,我想将具有读者和版本的图书 ID 合并为一行.读者和版本应该用,"连接起来.
I have three tables and I want to merge book ids that have readers and versions into one rows. Readers and Version should be concatenated with ','.
预订
Id name description
----------------------
1 Book1 Book 1 Title
2 Book2 Book 2 Title
3 Book3 Book 3 Title
4 Book5 Book 5 Title
图书阅读器
BookId Name
----------------------
1 James
2 Stephane
2 Michael
图书版本
BookId version
----------------------------------
1 v1
1 v2
2 v1
2 v2
2 v3
现在我使用这个查询
select
b.id as BookId, r.name as Reader, v.version as Version
from
Book as b
left outer join
BookReader as r on r.bookId = b.id
left outer join
BookVersion as v on v.bookId = b.id
我得到了这个结果:
BookId Reader Version
----------------------
1 James v1
1 James v2
2 Stephane v1
2 Stephane v2
2 Stephane V3
2 Michael v1
2 Michael v2
2 Michael V3
3 NULL NULL
4 NULL NULL
但我想要这样的结果:
BookId Reader Version
--------------------------------------------
1 James v1, v2
2 Stephane, Michael v1, v2, v3
3 NULL NULL
4 NULL NULL
最好的方法是什么?有 CTE 吗?或者有另一种方法?谢谢
What's the best way to do it ? With CTE ? or there's a another approach? Thanks
推荐答案
您可以使用 STUFF 生成 csv 值.下面是我平时用的.
You can use STUFF to generate csv values. Below is what I normally use.
CREATE TABLE #Book
(
Id INT ,
NAME VARCHAR(100) ,
Description VARCHAR(100)
);
CREATE TABLE #BookReader
(
BookId INT ,
Name VARCHAR(100)
);
CREATE TABLE #BookVersion
(
BookId INT ,
Version VARCHAR(10)
);
INSERT INTO #Book ( Id ,
NAME ,
Description )
VALUES ( 1 , -- Id - int
'Book1' , -- NAME - varchar(100)
'Book 1 Title' -- Description - varchar(100)
) ,
( 2 , -- Id - int
'Book2' , -- NAME - varchar(100)
'Book 2 Title' -- Description - varchar(100)
) ,
( 3 , -- Id - int
'Book3' , -- NAME - varchar(100)
'Book 3 Title' -- Description - varchar(100)
) ,
( 4 , -- Id - int
'Book5' , -- NAME - varchar(100)
'Book 5 Title' -- Description - varchar(100)
);
INSERT INTO #BookReader ( BookId ,
Name )
VALUES ( 1 , -- BookId - int
'James' -- Name - varchar(100)
) ,
( 2 , -- BookId - int
'Stephane' -- Name - varchar(100)
) ,
( 2 , -- BookId - int
'Michael' -- Name - varchar(100)
);
INSERT INTO #BookVersion ( BookId ,
Version )
VALUES ( 1 , -- BookId - int
'v1' -- Version - varchar(10)
) ,
( 1 , -- BookId - int
'v2' -- Version - varchar(10)
) ,
( 2 , -- BookId - int
'v1' -- Version - varchar(10)
) ,
( 2 , -- BookId - int
'v2' -- Version - varchar(10)
) ,
( 2 , -- BookId - int
'v3' -- Version - varchar(10)
);
SELECT b.Id ,
LTRIM(STUFF(( SELECT ', ' + br1.[Name] AS [text()]
FROM #BookReader br1
WHERE br1.BookId = b.Id
FOR XML PATH('')) ,
1 ,
1 ,
'')) AS Reader ,
LTRIM(STUFF(( SELECT ', ' + Version
FROM #BookVersion bv1
WHERE bv1.BookId = b.Id
FOR XML PATH('')) ,
1 ,
1 ,
'')) AS version
FROM #Book b;
DROP TABLE #Book;
DROP TABLE #BookReader;
DROP TABLE #BookVersion;
结果:
+----+--------------------+-------------+
| Id | Reader | Version |
+----+--------------------+-------------+
| 1 | James | v1, v2 |
| 2 | Stephane, Michael | v1, v2, v3 |
| 3 | NULL | NULL |
| 4 | NULL | NULL |
+----+--------------------+-------------+
这篇关于将多个数据行连接成一行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文