ListAGG w SQLSERVER

Próbuję zagregować pole 'STRING' w SQLServer. Chciałbym znaleźć taką samą funkcję LISTAGG jak w Oracle .

Czy wiesz, jak wykonać tę samą funkcję lub inną metodę?

Na Przykład, 

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

I chciałbym, aby wynik tego zapytania był 

1 | AB
2 | A
Author: n00b, 2013-03-18
Source

4 answers

Począwszy od SQL Server 2017 dostępna jest funkcja STRING_AGG, która znacznie upraszcza logikę: 

select FieldA, string_agg(FieldB, '') as data
from yourtable
group by FieldA

Zobacz SQL z Demo

W SQL Server możesz użyć FOR XML PATH aby uzyskać wynik: 

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;

Zobacz SQL z Demo

 81
Author: Taryn,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2019-12-12 16:11:30

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 Server

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)

  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

Zamawianie wymaga CTE lub subquery 

  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;

Bez zamawiania 

SELECT FieldA
     , GROUP_CONCAT(FieldB, ',') AS FieldBs
  FROM TableName
 GROUP BY FieldA
 ORDER BY FieldA;
 150
Author: Manas Kumar,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-04-15 21:42:00

W SQL Server 2017 dodaje się 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

Również, STRING_SPLIT jest przydatny w przeciwnym przypadku i dostępny w SQL Server 2016

 20
Author: vldmrrdjcc,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-08-25 14:55:21

To może się komuś przydać ..

Tj. do celów analityka danych i profilowania danych ..(tzn. nie pogrupowane według)..

Przed istnieniem funkcji String_agg SQL * Server 2017 ..

(tzn. zwraca tylko jeden wiersz ..)

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

Np. zwraca wartości rozdzielone przecinkami A, B

 1
Author: Allan F,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2019-01-31 01:21:21