string_agg - ListAGG en SQLSERVER

string_agg order by sql server

Question

Estoy intentando agregar un campo ''STRING'' en SQLServer. Me gustaría encontrar la misma función que LISTAGG en Oracle.

¿Sabes cómo hacer la misma función u otro método?

Por ejemplo,

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

Y me gustaría que el resultado de esta consulta sea

1 | AB
2 | A

Answer 1

MySQL

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

Oracle y 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;

servidor SQL

SQL Server ≥ 2017 y SQL Azure

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

SQL Server ≤ 2016 (CTE incluido para fomentar el principio DRY )

  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

Ordenar requiere un CTE o subconsulta

  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;

Sin pedir

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

Answer 2

En SQL Server 2017 se agrega 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

Además, STRING_SPLIT es útil para el caso contrario y está disponible en SQL Server 2016

Answer 3

En SQL Server puede usar FOR XML PATH para obtener el resultado:

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;

Ver SQL Fiddle con demostración