Andrew Fraser DBA

listagg xml path Oracle Microsoft Sql Server

If you have a table with rows like this:

typeName custName
-------- --------
typeA    cust1
typeA    cust2
typeA    cust3
typeB    cust1
typeB    cust4
typeB    cust5
typeB    cust6

And you want to display it’s data aggregated like this:

typeName COUNT(*) fullListing
-------- -------- -----------------------------
typeA           3 cust1 , cust2 , cust3
typeB           4 cust1 , cust4 , cust5 , cust6

Oracle:

WITH demoTable ( typeName , custName ) AS  (  
   SELECT 'typeA' , 'cust1' FROM DUAL UNION ALL
   SELECT 'typeA' , 'cust2' FROM DUAL UNION ALL
   SELECT 'typeA' , 'cust3' FROM DUAL UNION ALL
   SELECT 'typeB' , 'cust4' FROM DUAL UNION ALL
   SELECT 'typeB' , 'cust5' FROM DUAL UNION ALL
   SELECT 'typeB' , 'cust6' FROM DUAL UNION ALL
   SELECT 'typeB' , 'cust1' FROM DUAL
)
SELECT d.typeName
     , COUNT(*)
     , LISTAGG ( d.custName , ' , ' ) WITHIN GROUP ( ORDER BY d.custName ) AS fullListing
  FROM demoTable d
 GROUP BY d.typeName
 ORDER BY d.typeName
;

Microsoft Sql Server – note how the table name is required twice:

WITH demoTable ( typeName , custName ) AS  (  
   SELECT 'typeA' , 'cust1' UNION ALL
   SELECT 'typeA' , 'cust2' UNION ALL
   SELECT 'typeA' , 'cust3' UNION ALL
   SELECT 'typeB' , 'cust4' UNION ALL
   SELECT 'typeB' , 'cust5' UNION ALL
   SELECT 'typeB' , 'cust6' UNION ALL
   SELECT 'typeB' , 'cust1'
)
SELECT d.typeName
     , COUNT(*)
     , ( SELECT d2.custName + ' , '
           FROM demoTable d2
          WHERE d2.typeName = d.typeName
          ORDER BY d2.custName
            FOR XML PATH ( '' )
       ) AS fullListing
  FROM demoTable d
 GROUP BY d.typeName
 ORDER BY d.typeName
;