ListAGG în SQLSERVER (Programare, Sql, Server Sql, Agregat)

utilizator1557642 a intrebat.

Încerc să agreg un câmp ‘STRING’ în SQLServer. Aș dori să găsesc aceeași funcție LISTAGG ca în Oracle .

Știți cum se face aceeași funcție sau o altă metodă?

De exemplu,

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

Și aș vrea ca rezultatul acestei interogări să fie

1 | AB
2 | A

Comentarii

4 răspunsuri
Taryn

Începând din SQL Server 2017 STRING_AGG este disponibilă funcția care simplifică considerabil logica:

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

A se vedea SQL Fiddle cu demonstrație

În SQL Server puteți utiliza FOR XML PATH pentru a obține rezultatul:

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;

A se vedea SQL Fiddle with Demo

Comentarii

  • aș dori să introduc o clauză order by în sub-selecția. Știți cum se introduce această clauză? –  > Por user1557642.
  • @user1557642 Ce doriți să comandați? Doriți ca valorile să fie inversate? Puteți adăuga cu ușurință o clauză ORDER BY vezi acest demo… sqlfiddle.com/#!3/0836c/7. În acest caz, rezultatul pentru FieldA va arăta BA –  > Por Taryn.
  • de fapt, am 10 linii de agregat. Dar SQLServer face o clasificare alfabetică înainte de agregare. Aș vrea să agreg cu parametrii mei(un al treilea câmp) și nu ordinea alfabetică. –  > Por utilizator1557642.
  • @user1557642 Puteți utiliza un ORDER BY, , va trebui doar să includeți un GROUP BY în subîntrebare, vezi acest demo — sqlfiddle.com/#!3/0836c/7 –  > Por Taryn.
  • pauze pentru șirurile cu caractere speciale precum ‘<‘; acestea sunt codificate în XML. Nu este deloc o soluție. Hack total, bazat pe comportamentul învechit și nedocumentat al ‘for xml path’. –  > Por Triynko.
Manas Kumar

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 ≤ 2016 (CTE inclus pentru a încuraja principiul 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

Ordonarea necesită un CTE sau o subinterogare

  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;

Fără ordonare

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

Comentarii

  • răspuns excelent – un pic mai concis decât răspunsul marcat + menționează și alte dialecte sql. Sintaxa sqlserver pentru acest lucru este într-adevăr neintuitivă. Am fost, de asemenea, surprins că parantezele suplimentare din jurul select in stuff sunt necesare. –  > Por JonnyRaa.
  • @manas_kumar dar… de ce folosești STUFF? este inutil. Îmi scapă ceva? –  > Por Ricardo C.
  • @Ricardo_C STUFF este folosit aici pentru a elimina virgula de început. –  > Por DigitalDan.
  • Pentru a fi complet, acest lucru trebuie adăugat la exemplul serverului sql: stackoverflow.com/a/8856789 Împiedică înlocuirea anumitor caractere cu etichete XML. –  > Por Wouter.
  • @DanLenski Nu pare să fie cazul, deoarece fac referire la un cte cu succes pe celălalt monitor al meu chiar acum, îmi imaginez că nu ați construit corect interogarea? –  > Por iamdave.
vldmrrdjcc

În SQL Server 2017 STRING_AGG este adăugat:

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

De asemenea, STRING_SPLIT este util pentru cazul opus și este disponibil în SQL Server 2016.

Comentarii

  • STRING_AGG / STRING_SPLIT au fost adăugate recent și în Azure SQL Database, de asemenea, pentru oricine se întâmplă să aterizeze aici pe acea platformă. –  > Por Brian Jorden.
  • Notă: vNext a fost denumit SQL Server 2017 – SQL Server 2017 -.  > Por Charles Burns.
  • Știți cum să utilizați distinct c.name, astfel încât toate numele concatenate să fie unice? –  > Por Sergey Malyutin.
Allan F

Acest lucru ar putea fi util și pentru cineva …

Adică pentru un analist de date și pentru scopuri de tip profilare a datelor …(adică nu grupate după) …

Înainte de existența funcției SQL*Server 2017 String_agg …

(adică returnează doar un singur rând ..)

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 

de exemplu, returnează valorile separate prin virgulă A,B