SQLSERVER의 ListAGG

SQLSERVER의 ListAGG

---

SQLServer에서 'STRING'필드를 집계하려고합니다. Oracle과 같은 LISTAGG 기능을 찾고 싶습니다.

동일한 기능 또는 다른 방법을 수행하는 방법을 알고 있습니까?

예를 들어

Field A | Field B
1       |  A
1       |  B
2       |  A

그리고이 쿼리의 결과가

1 | AB
2 | A

---

SQL Server에서는 다음을 사용 FOR XML PATH하여 결과를 얻을 수 있습니다.

select distinct t1.FieldA,
  STUFF((SELECT distinct '' + t2.FieldB
         from yourtable t2
         where t1.FieldA = t2.FieldA
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,0,'') data
from yourtable t1;

데모로 SQL Fiddle 보기

---

MySQL

SELECT FieldA
     , GROUP_CONCAT(FieldB ORDER BY FieldB SEPARATOR ',') AS FieldBs
  FROM TableName
 GROUP BY FieldA
 ORDER BY FieldA;

Oracle 및 DB2

SELECT FieldA
     , LISTAGG(FieldB, ',') WITHIN GROUP (ORDER BY FieldB) AS FieldBs
  FROM TableName
 GROUP BY FieldA
 ORDER BY FieldA;

PostgreSQL

SELECT FieldA
     , STRING_AGG(FieldB, ',' ORDER BY FieldB) AS FieldBs
  FROM TableName
 GROUP BY FieldA
 ORDER BY FieldA;

SQL 서버

SQL Server ≥ 2017 및 Azure SQL

SELECT FieldA
     , STRING_AGG(FieldB, ',') WITHIN GROUP (ORDER BY FieldB) AS FieldBs
  FROM TableName
 GROUP BY FieldA
 ORDER BY FieldA;

SQL Server ≤ 2016 ( DRY 원칙 을 장려하기 위해 CTE 포함 )

  WITH CTE_TableName AS (
       SELECT FieldA, FieldB
         FROM TableName)
SELECT t0.FieldA
     , STUFF((
       SELECT ',' + t1.FieldB
         FROM CTE_TableName t1
        WHERE t1.FieldA = t0.FieldA
        ORDER BY t1.FieldB
          FOR XML PATH('')), 1, LEN(','), '') AS FieldBs
  FROM CTE_TableName t0
 GROUP BY t0.FieldA
 ORDER BY FieldA;

SQLite

주문하려면 CTE 또는 하위 쿼리가 필요합니다.

  WITH CTE_TableName AS (
       SELECT FieldA, FieldB
         FROM TableName
        ORDER BY FieldA, FieldB)
SELECT FieldA
     , GROUP_CONCAT(FieldB, ',') AS FieldBs
  FROM CTE_TableName
 GROUP BY FieldA
 ORDER BY FieldA;

주문하지 않고

SELECT FieldA
     , GROUP_CONCAT(FieldB, ',') AS FieldBs
  FROM TableName
 GROUP BY FieldA
 ORDER BY FieldA;

---

SQL Server 2017에서 STRING_AGG 가 추가되었습니다.

SELECT t.name,STRING_AGG (c.name, ',') AS csv
FROM sys.tables t
JOIN sys.columns c on t.object_id = c.object_id
GROUP BY t.name
ORDER BY 1

또한 STRING_SPLIT 는 반대의 경우에 유용하며 SQL Server 2016에서 사용할 수 있습니다.

---

이것은 누군가에게도 유용 할 수 있습니다 ..

i.e. For a data analyst and data profiling type of purposes ..(i.e. not grouped by) ..

Prior to the SQL*Server 2017 String_agg function existence ..

(i.e. returns just one row ..)

select distinct
SUBSTRING (
stuff(( select distinct ',' + [FieldB] from tablename order by 1 FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') 
,1,0,'' ) 
,2,9999)
from 
    tablename 

e.g. returns comma separated values A,B

참고URL : https://stackoverflow.com/questions/15477743/listagg-in-sqlserver