[Challenge] 建立 SQL Server 版本的 Oracle WM_CONCAT() function

Beyond Relational TSQL Beginners Challenge 21

This challenge deals with String aggregation. On occasion it is necessary to aggregate data from a number of rows into a single row, giving a list of data associated with a specific value.

Oracle has an inbuilt function "WM_CONCAT" to aggregate data from a number of rows into a single row. Now, Let us implement this in TSQL.

WM_CONCAT aggregates data from a number of rows into a single row, giving a list of data associated with a specified value.

• Sample Data

Deptno Empname
------ -------
10    Niladri
20    Jeeva
30    Deepak
30    Prantik
20    Arina

• Expected Results

Deptno Empname
-----  --------------
10    Niladri
20    Arina,Jeeva
30    Deepak,Prantik

• Rules
1. Deptno should be sorted in Ascending Order.
2. Empname should be sorted in Ascending Order.
3. The program should run in SQL SERVER 2005 and above.
4. The output should be in the same way as it has been shown.

• 個人解法

DECLARE @t TABLE(Deptno INT, Empname VARCHAR(20))
INSERT INTO @t 
SELECT 10, 'Niladri' UNION ALL
SELECT 20, 'Jeeva' UNION ALL
SELECT 30, 'Deepak' UNION ALL
SELECT 30, 'Prantik' UNION ALL
SELECT 20, 'Arina' 

SELECT T1.Deptno ,
  STUFF
    (
      (
      SELECT ',' + T2.Empname
      FROM @t AS T2
      WHERE T2.DeptNO = T1.DeptNO
      ORDER BY T2.Empname
      FOR XML PATH('')
      )
      ,1,1,''
    ) AS Empname
FROM @t AS T1
GROUP BY T1.Deptno
ORDER BY Deptno

參考資料
• TSQL Beginners Challenge 21 - Create SQL Server version of the ORACLE WM_CONCAT function