将单个列连接成逗号分隔列表

Question

将单个列连接成逗号分隔列表

6

我看到过许多人将单个列卷成逗号分隔列表的示例，但我需要更多。

这里是我需要的数据和结果示例。

DECLARE @SalesPerson table (SalesPersonID int, SalesPersonName varchar(10))
DECLARE @Region table (RegionID int, RegionName varchar(15))
DECLARE @SalesPersonRegion table (SalesPersonID int, RegionID int)

INSERT INTO @SalesPerson (SalesPersonID, SalesPersonName) VALUES (1,'Jeff') 
INSERT INTO @SalesPerson (SalesPersonID, SalesPersonName) VALUES (2,'Pat') 
INSERT INTO @SalesPerson (SalesPersonID, SalesPersonName) VALUES (3,'Joe') 

INSERT INTO @Region (RegionID, RegionName) VALUES (1,'North') 
INSERT INTO @Region (RegionID, RegionName) VALUES (2,'South') 
INSERT INTO @Region (RegionID, RegionName) VALUES (3,'East') 
INSERT INTO @Region (RegionID, RegionName) VALUES (4,'West') 

INSERT INTO @SalesPersonRegion (SalesPersonID,RegionID) VALUES (1,1)
INSERT INTO @SalesPersonRegion (SalesPersonID,RegionID) VALUES (1,2)
INSERT INTO @SalesPersonRegion (SalesPersonID,RegionID) VALUES (1,3)
INSERT INTO @SalesPersonRegion (SalesPersonID,RegionID) VALUES (2,2)
INSERT INTO @SalesPersonRegion (SalesPersonID,RegionID) VALUES (2,3)
INSERT INTO @SalesPersonRegion (SalesPersonID,RegionID) VALUES (2,4)
INSERT INTO @SalesPersonRegion (SalesPersonID,RegionID) VALUES (3,1)
INSERT INTO @SalesPersonRegion (SalesPersonID,RegionID) VALUES (3,4)

一个简单的选择语句将会得到每个销售人员以及他们所在地区的信息。

SELECT 
    sp.SalesPersonID,
    sp.SalesPersonName,
    r.RegionName
FROM @SalesPersonRegion spr
    JOIN @SalesPerson sp
        ON spr.SalesPersonID = sp.SalesPersonID
    JOIN @Region r
        ON spr.RegionID = r.RegionID

在这种情况下，它将返回9行。

我想要像这样的结果：

SalesPersonID    SalesPersonName      Regions
1                Jeff                 North,South,East
2                Pat                  South,East,West
3                Joe                  North,West

- Jeff Reddy

1

看一下这个方法：http://codecorner.galanter.net/2009/06/25/t-sql-string-aggregate-in-sql-server/ - Yuriy Galanter

FOR XML或CONCAT - Bogdan Sahlean

3个回答

2

尝试使用以下查询：

SELECT 
    sp.SalesPersonID,
    sp.SalesPersonName,
    reg.Regions
FROM @SalesPerson sp
    CROSS APPLY( -- or OUTER APPLY
        SELECT STUFF(
        (SELECT ','+r.RegionName
        FROM    @Region r INNER JOIN @SalesPersonRegion spr ON r.RegionID = spr.RegionID
        WHERE   spr.SalesPersonID = sp.SalesPersonID
        FOR XML PATH('')),1,1,'') AS Regions
    )reg;

结果：

SalesPersonID SalesPersonName Regions
------------- --------------- ----------------
1             Jeff            North,South,East
2             Pat             South,East,West
3             Joe             North,West

- Bogdan Sahlean

我认为CROSS APPLY和额外的嵌套SELECT只是不必要地使事情变得复杂了。 - Aaron Bertrand

在这种情况下，这是风格问题。通常，我喜欢在父查询的FROM子句中找到/查看所有源表/视图等。 - Bogdan Sahlean

2

select
    sp.SalesPersonID,
    sp.SalesPersonName,
    stuff(
       (
           select ',' + r.RegionName
           from @SalesPersonRegion as spr 
               inner join @Region as r on r.RegionID = spr.RegionID
           where spr.SalesPersonID = sp.SalesPersonID
           for xml path(''), type
       ).value('.', 'nvarchar(max)')
       , 1, 1, '')
from @SalesPerson as sp

请参考SQL Fiddle示例。

- Roman Pekar

1

这与我的答案有何不同？ - Aaron Bertrand

1

它没有[1]，但总的来说，你是对的，这个是多余的 - 我花了太多时间在格式化和SQL调试上。 - Roman Pekar

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Aaron Bertrand · Accepted Answer

SELECT 
  sp.SalesPersonID, 
  sp.SalesPersonName, 
  Regions = STUFF
  (
    (
      SELECT ',' + r.RegionName
       FROM @Region AS r
       INNER JOIN @SalesPersonRegion AS spr
       ON r.RegionID = spr.RegionID
       WHERE spr.SalesPersonID = sp.SalesPersonID
       ORDER BY r.RegionID
       FOR XML PATH(''), TYPE
    ).value('.[1]','nvarchar(max)'),
    1,1,''
  )
FROM @SalesPerson AS sp
ORDER BY sp.SalesPersonID;