設計資料庫，常常會有一對多邏輯介面（檔頭、檔身設計），這樣的設計在產生報表時，為方便使用者閱讀，都會希望把檔身的多筆資料，整合在一行內(多筆字串資料合併成一行字串)。
公司在月底時，人事通常會提供一份報表 - 下個月員工排休表，給課長確認是否有哪一天太多人排休，會導致產線人力不足，就利用這份排休表來說明這個主題。
-- 建立測試資料
IF OBJECT_ID (N'Leave',N'U') IS NOT NULL
DROP TABLE Leave
CREATE TABLE Leave (Date datetime,EmpName varchar(8))
INSERT INTO Leave VALUES('2011-08-01','張三')
INSERT INTO Leave VALUES('2011-08-02','張三')
INSERT INTO Leave VALUES('2011-08-22','張三')
INSERT INTO Leave VALUES('2011-08-01','李四')
INSERT INTO Leave VALUES('2011-08-22','李四')
INSERT INTO Leave VALUES('2011-08-01','王五')
INSERT INTO Leave VALUES('2011-08-23','王五')
INSERT INTO Leave VALUES('2011-08-24','王五')
INSERT INTO Leave VALUES('2011-08-25','王五')
使用者自訂函數
IF OBJECT_ID(N'dbo.getCommaString', N'FN') IS NOT NULL
DROP FUNCTION dbo.getCommaString
-- 建立 FUNCTION
CREATE FUNCTION getCommaString(@date datetime)
RETURNS varchar(500) --500若不夠，請加大
BEGIN
DECLARE @string varchar(500) --500若不夠，請加大
SET @string = ''
SELECT @string = @string + EmpName + ','
FROM Leave
WHERE Date = @date
ORDER BY EmpName
SET @string = LEFT(@string,LEN(@string)-1) -- 把最後面的逗號後刪除
RETURN @string
END
-- 使用自定函數來進行字串連結
SELECT
CONVERT(char(10),Date,120) AS [日期],
DATENAME(dw,Date) AS [星期],
dbo.getCommaString(Date) AS [請假人員]
FROM Leave
GROUP BY Date
Cursor
-- 宣告一個 Table 變數來承接 CURSOR 跑完的結果
DECLARE @Temp table (Date datetime,String varchar(100))
DECLARE curTemp CURSOR
FOR
(
SELECT Date
FROM Leave
GROUP BY Date
)
OPEN curTemp
DECLARE @date as datetime
DECLARE @string as varchar(100)
FETCH NEXT FROM curTemp INTO @date
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @string = ''
SELECT @string = @string + EmpName + ','
FROM Leave
WHERE Date = @date
SET @string = LEFT(@string,LEN(@string)-1)
INSERT INTO @Temp (Date,String)
VALUES(@date,@string)
FETCH NEXT FROM curTemp INTO @date
END
CLOSE curTemp
DEALLOCATE curTemp
SELECT
CONVERT(char(10),Date,120) AS [日期],
DATENAME(dw,Date) AS [星期],
String AS [請假人員]
FROM @Temp
CTE
;
WITH CTE AS
(
SELECT
Date ,
CAST('' AS nvarchar(100)) AS Data ,
CAST('' AS nvarchar(100)) AS String ,
0 AS Counts ,
COUNT(*) AS Total -- 資料總筆數
FROM Leave
GROUP BY Date
UNION ALL
SELECT
L.Date ,
CAST(L.EmpName AS NVARCHAR(100)) ,
CAST(String + ',' + L.EmpName AS nvarchar(100)) ,
Counts + 1 ,
Total
FROM CTE AS T
JOIN Leave AS L ON T.Date = L.Date
AND T.Data < L.EmpName -- 遞迴停止重要關鍵
)
SELECT
CONVERT(char(10),Date,120) AS [日期],
DATENAME(dw,Date) AS [星期],
STUFF(String,1,1,'') AS [請假人員]
FROM CTE
WHERE Counts = Total -- 利用資料總筆來判斷哪一筆是最後一筆
ORDER BY Date
FOR XML PATH 應用
SELECT
Date AS 日期,
DATENAME(dw,Date) AS 星期 ,
(
STUFF -- 說明 3
(
(
SELECT ',' + EmpName -- 說明 2
FROM LEave T2
WHERE T2.Date = T1.Date
FOR XML PATH('') -- 說明 1
)
, 1, 1, ''
)
) AS [請假人員]
FROM Leave T1
GROUP BY Date
- 說明
- 依預設，PATH 模式會針對結果集的每個資料列產生 <row> 元素包裝函數。 您可以選擇性地指定元素名稱。 如果您有選擇，則會將指定名稱做為包裝函數的元素名稱。 如果您提供空白字串 (FOR XML PATH (''))，就不會產生包裝函數元素。
- 不要給欄位名稱，任何沒有名稱的資料行都將予以內嵌。
- 利用 STUFF() 來取代第一個逗號
STRING_AGG()
SQL Server 2017 推出的新功能
SELECT
[Date] AS 日期,
DATENAME(dw,Date) AS 星期 ,
STRING_AGG(EmpName , ',') AS [請假人員]
FROM Leave
GROUP BY [Date]
這五種作法中，CURSOR 和 CTE 比較不會應用在實務上，個人練習用。
![[SQL] 字串欄位轉置 - 1](https://lh3.googleusercontent.com/blogger_img_proxy/AEn0k_sUrhrza2A8J4s7rPCzq15M-6YX7Tjc2rxRD8EU08_XjfmSHZ0E8p9cvqDCKKV56wLRdpDlUdyy5cBcfOF5C215hQV5JrvhSQKJ6cA0EWiOOTeKrmWWVEWajC9yWA5OexY7lA=s0-d)
