問題描述

我見過多個示例，人們將單列匯總為逗號分隔的列表，但我還需要更多.

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;

這篇關于將單列連接成逗號分隔的列表的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！