问题描述
我见过多个示例,人们将单列汇总为逗号分隔的列表,但我还需要更多.
I've seen multiple examples of people rolling up a single column into a comma delimited list, but I need a bit more.
这是我需要的数据和结果的示例.
Here is an example of the data and results I need.
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)
一个简单的选择将让我得到每个销售人员,以及该销售人员所在的每个区域.
A simple select will get me each sales person, with each of that salesperson's region.
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 行.
In this case, it will return 9 Rows.
我想得到这样的结果:
SalesPersonID SalesPersonName Regions 1 Jeff North,South,East 2 Pat South,East,West 3 Joe North,West
推荐答案
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;