Listagg in Sqlserver

ListAGG in SQLSERVER

Starting in SQL Server 2017 the STRING_AGG function is available which simplifies the logic considerably:

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

See SQL Fiddle with Demo

In SQL Server you can use FOR XML PATH to get the result:

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;

See SQL Fiddle with Demo

Grouped string aggregation / LISTAGG for SQL Server

http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/

It is an interesting problem in Transact SQL, for which there are a number of solutions and considerable debate. How do you go about producing a summary result in which a distinguishing column from each row in each particular category is listed in a 'aggregate' column? A simple, and intuitive way of displaying data is surprisingly difficult to achieve. Anith Sen gives a summary of different ways, and offers words of caution over the one you choose...

How to convert Oracle LISTAGG() function to SQL Server using Stuff()

I solved it, below is the script

Select 
Col1, Col2, Col3, Col4,
'BDS: ' + STUFF (select ' , ' + BD
                 from (select Col1, Col2, Col3, Col4, Col5, Col6,
                       CONVERT(VARCHAR,Col7) + '-' + Col8+ '-' + CONVERT(VARCHAR,Col5)   + '-' + CONVERT(VARCHAR,Col6) BD
                       from TBL1) A1
                 where A1.col1 =A2.col1 and
                       A1.col2 = A2.col2 and
                       A1.col3 = A2.col3 and
                       A1.col4 = A2.col4 and
                       A1.col5 = A2.col5 and
                       A1.col6 = A2.col6
                 order by Col5, Col6    
                 FOR XML PATH('')), 1, 2, '') + '.' AS BDS    
from 
(select 
    Col1, Col2, Col3, Col4, Col5, Col6,
    CONVERT(VARCHAR,Col7) + '-' + Col8+ '-' + CONVERT(VARCHAR,Col5)   + '-' + CONVERT    (VARCHAR,Col6) BD
     from TBL1) A2
Group By 
Col1, Col2, Col3, Col4

Need to Convert query from Oracle to SQL Server and need to convert a ListAgg

You can try this.

;WITH CTE AS (
    select 
        A.CONTRACT_ID,
        A.CONTRACT_ref_num,
        A.BA_INT_ID,
        B.WHOLE_NAME
    from 
        MN_BA_INT A
        INNER JOIN MN_BA_INT_WHOLE B ON A.BA_INT_ID=B.BA_INT_ID 
        INNER JOIN MN_STRUCTURED_DOC C ON A.CONTRACT_ID=C.struct_doc_id 
)
SELECT CONTRACT_ID, CONTRACT_ref_num, BA_INT_ID, 
    STUFF( ( SELECT  ' | ' + WHOLE_NAME 
        FROM CTE T2 
    WHERE T1.CONTRACT_ID = T2.CONTRACT_ID 
        AND T1.CONTRACT_ref_num = T2.CONTRACT_ref_num 
        AND T1.BA_INT_ID = T2.BA_INT_ID 
    ORDER BY CONTRACT_ID DESC FOR XML PATH('') ) ,1,3,'') AS Distributors FROM CTE T1   
group by 
    CONTRACT_ID, CONTRACT_ref_num, BA_INT_ID

---

Related Topics