如何使用逗號合併查詢結果

如何使用逗號合併多筆資料到同一欄位

有時候我們會想要將多筆資料合併為一筆顯示，如下面資料中，若想要將同一個 TicketID 的 Person 用逗號間隔，可以這麼做：

CREATE  TABLE Tickets (
    [TicketID] char(5) NOT NULL,
    [Person] nvarchar(15) NOT NULL
)

INSERT INTO Tickets VALUES
    ('T0001', 'Alice'),
    ('T0001', 'Bob'),
    ('T0002', 'Catherine'),
    ('T0002', 'Doug'),
    ('T0003', 'Elaine')

使用 Cursor

使用 Cursor 就是跑迴圈。

ALTER PROCEDURE usp_GetPersonList
(@TicketId varchar(max))
AS
    DECLARE @Person varchar(Max);
    DECLARE @PersonList varchar(Max);
    
    DECLARE _cursor CURSOR FOR
    SELECT Person FROM Tickets WHERE TicketId=@TicketId
 
    OPEN _cursor
    FETCH NEXT FROM _cursor INTO @Person
 
	SET @PersonList='';
    WHILE @@FETCH_STATUS = 0
    BEGIN
        Set @PersonList = @PersonList + ',' + @Person
		FETCH NEXT FROM _cursor INTO @Person
    END
    
    IF LEN(@PersonList)>0
		SET @PersonList = Substring(@PersonList,2,LEN(@PersonList));
		
	SELECT @PersonList;

CLOSE _cursor
DEALLOCATE _cursor
GO

EXEC usp_GetPersonList 'T0001'
GO

使用變數合併結果

上面的迴圈方法，也可以直接使用一個變數來累加結果，可以更簡便。

DECLARE @PersonList varchar(MAX)

SELECT @PersonList = COALESCE(@PersonList + ',' , '') + Person
FROM Tickets WHERE TicketID='T0001'

SELECT @PersonList

• 上面用到 COALESCE，只是為了方便處理第一個逗號而已。

使用 FOR XML Path

• FOR XML ：以資料列集（rowset）的形式取得查詢結果。

1. 首先透過 FOR XML 子句，可以將結果回傳成單一的 XML 格式。

SELECT (',' + Person) 
FROM Tickets 
WHERE TicketID='T0001'
FOR XML AUTO('')

2. 若是要取得所有群組。

SELECT B.TicketID, 
	(
	SELECT (',' + Person) 
	FROM Tickets A
	WHERE A.TicketID=B.TicketID
	FOR XML PATH('') 
	)
FROM Tickets B	
GROUP BY TicketID

3. 修飾第一個逗號。

使用 STUFF 將第一個字取代掉。

SELECT B.TicketID, 
	STUFF((
	SELECT (',' + Person) 
	FROM Tickets A
	WHERE A.TicketID=B.TicketID
	FOR XML PATH('') 
	) , 1, 1, '')
FROM Tickets B	
GROUP BY TicketID

如何將逗號間隔資料分成不同資料列

如果有一資料如下：

CREATE  TABLE TicketList (
    [TicketID] char(5) NOT NULL,
    [PersonList] nvarchar(max) NOT NULL
)

INSERT INTO TicketList VALUES
    ('T0001', 'Alice,Bob'),
    ('T0002', 'Catherine,Doug,Elaine')

想拆成以下結果：

查詢語法：

;WITH tmp(TicketID, Person, PersonList) as (

select TicketID, LEFT(PersonList, CHARINDEX(',',PersonList+',')-1),
    STUFF(PersonList, 1, CHARINDEX(',',PersonList+','), '')
from TicketList

union all

select TicketID, LEFT(PersonList, CHARINDEX(',',PersonList+',')-1),
    STUFF(PersonList, 1, CHARINDEX(',',PersonList+','), '')
from tmp
where PersonList > ''

)

select TicketID, Person
from tmp
order by TicketID

結果：