SQL Server 连接 GROUP BY [英] SQL Server Concatenate GROUP BY
问题描述
我有一个类似这样的查询
I have a query that looks like this
SELECT J.JobID,T.Title FROM JobsTagMap J
Left Join Tags T
ON J.TagID=T.TagID
即返回如下数据集(简而言之,JobID实际上是一个UniqueIdentifier)
That returns the following dataset (simplified, JobID is actually a UniqueIdentifier)
JobID Title
1 Tag1
1 Tag2
2 Tag2
2 Tag5
2 Tag9
现在,我想通过 JobID 列对其进行分组并连接标题,因此结果如下
Now, i'd like to group this by the JobID-column and concatenate the Title, so the results is as following
JobID Title
1 Tag1,Tag2
2 Tag2,Tag5,Tag9
我该怎么做?
推荐答案
如果您使用的是 sql server 2005+.然后你可以这样做:
If you are using sql server 2005+. Then you can do like this:
SELECT
JobsTagMap.JobID,
STUFF
(
(
SELECT
',' +Title
FROM
Tags
WHERE
Tags.TagID=JobsTagMap.TagID
FOR XML PATH('')
)
,1,1,'') AS Title
FROM JobsTagMap
编辑
因为您没有向我们展示表结构和不同表中的数据.这有点难知道.所以我假设你的表结构看起来像这样:
Because you did not show us the table structure and the data in the different tables. It was a lite bit hard to know. So I assume that your table structure looks something like this:
CREATE TABLE JobsTagMap
(
JobID INT,
TagID INT
)
CREATE TABLE Tags
(
TagID INT,
Title VARCHAR(100)
)
使用这些数据:
INSERT INTO JobsTagMap
VALUES(1,1),(1,2),(2,2),(2,4),(2,5)
INSERT INTO Tags
VALUES(1,'Tag1'),(2,'Tag2'),(3,'Tag2'),(4,'Tag5'),(5,'Tag9')
如果您正在获取数据,则您显示的 JobID
不能是唯一的.您可能在它唯一的某处有一个 Job
表.如果您只想使用您显示的这些表格,那么您需要执行以下操作:
If you are getting that data that you are showing the JobID
cannot be unique. You might have the a Job
table somewhere where it is unique. If you just want to use these table that you are showing then you need to do something like this:
;WITH CTE
AS
(
SELECT
ROW_NUMBER() OVER(PARTITION BY JobID ORDER BY JobID) AS RowNbr,
JobsTagMap.*
FROM
JobsTagMap
)
SELECT
*,
STUFF
(
(
SELECT
',' +Title
FROM
Tags
JOIN JobsTagMap
ON Tags.TagID=JobsTagMap.TagID
WHERE
JobsTagMap.JobID=CTE.JobID
FOR XML PATH('')
)
,1,1,'') AS Title
FROM
CTE
WHERE
CTE.RowNbr=1
这会让你得到这个结果:
This will get you this result:
1 1 1 Tag1,Tag2
1 2 2 Tag2,Tag5,Tag9
所以以后总是显示什么表结构和它数据.那会给你更好的答案
So in the future always show what table structure and it data. That will give you better answers
这篇关于SQL Server 连接 GROUP BY的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!