~楓花雪岳~: [SQL] 群組字串連結

星期五, 10月 14, 2011

[SQL] 群組字串連結

設計資料庫，常常會有一對多邏輯介面（檔頭、檔身設計），這樣的設計在產生報表時，為方便使用者閱讀，都會希望把檔身的多筆資料，整合在一行內(多筆字串資料合併成一行字串)。

公司在月底時，人事通常會提供一份報表 - 下個月員工排休表，給課長確認是否有哪一天太多人排休，會導致產線人力不足，就利用這份排休表來說明這個主題。

-- 建立測試資料
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 比較不會應用在實務上，個人練習用。

~楓花雪岳~

星期五, 10月 14, 2011

[SQL] 群組字串連結

沒有留言:

張貼留言