SQL Query to Get Aggregated Result in Comma Separators Along With Group by Column in SQL Server

SQL Query to get aggregated result in comma separators along with group by column in SQL Server

You want to use FOR XML PATH construct:

select 
    ID, 
    stuff((select ', ' + Value 
           from YourTable t2 where t1.ID = t2.ID 
           for xml path('')),
          1,2,'') [Values]
from YourTable t1
group by ID

The STUFF function is to get rid of the leading ', '.

You can also see another examples here:

• SQL same unit between two tables needs order numbers in 1 cell
• SQL and Coldfusion left join tables getting duplicate results as a list in one column

SQL comma-separated row with Group By clause

You want to use FOR XML PATH construct:

SELECT ACCOUNT, 
       unit, 
       SUM(state_fee), 
       Stuff((SELECT ', ' + code 
              FROM   tblmta t2 
              WHERE  t2.ACCOUNT = t1.ACCOUNT 
                     AND t2.unit = t1.unit 
                     AND t2.id = '123' 
              FOR XML PATH('')), 1, 2, '') [Codes] 
FROM   tblmta t1 
WHERE  t1.id = '123' 
GROUP  BY ACCOUNT, 
          unit 

See other examples here:

• SQL same unit between two tables needs order numbers in 1 cell
• SQL Query to get aggregated result in comma seperators along with group by column in SQL Server

SQL Server : GROUP BY clause to get comma-separated values

try this:

SELECT ReportId, Email = 
    STUFF((SELECT ', ' + Email
           FROM your_table b 
           WHERE b.ReportId = a.ReportId 
          FOR XML PATH('')), 1, 2, '')
FROM your_table a
GROUP BY ReportId

SQL fiddle demo

Comma separated results in SQL

Update (As suggested by @Aaron in the comment)

STRING_AGG is the preferred way of doing this in the modern versions of SQL Server.

Original Answer:

Use FOR XML PATH('') - which is converting the entries to a comma separated string and STUFF() -which is to trim the first comma- as follows Which gives you the same comma separated result

SELECT  STUFF((SELECT  ',' + INSTITUTIONNAME
            FROM EDUCATION EE
            WHERE  EE.STUDENTNUMBER=E.STUDENTNUMBER
            ORDER BY sortOrder
            FOR XML PATH(''), TYPE).value('text()[1]','nvarchar(max)')
           , 1, LEN(','), '') AS listStr

FROM EDUCATION E
GROUP BY E.STUDENTNUMBER

Here is the FIDDLE

Group by with comma-separated values and excluding the value from the previous column value

This can be done using For XML Path("), TYPE as follows:

SELECT S.id, S.Fname, S.Lname, S.Lid, 
       STUFF((SELECT Concat(',',Lid) FROM StudentLecturer WHERE id=S.id And Lid<>S.Lid
       FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)'),1,1,'') AS [Concat Values]  
FROM StudentLecturer As S 

with using String_Agg function for SQL Server 2017 (14.x) and later versions.

SELECT S.id, S.Fname, S.Lname, S.Lid, 
       STRING_AGG(S_1.Lid, ',') WITHIN GROUP (ORDER BY S_1.Lid) AS [Concat Values]  
FROM StudentLecturer AS S LEFT JOIN StudentLecturer AS S_1 ON (S.id=S_1.id AND
                                                               S.Lid<>S_1.Lid)
GROUP BY S.id, S.Fname, S.Lname, S.Lid
ORDER BY S.Fname, S.Lname, S.Lid

How to get strings comma separated in ascending order with STRING_AGG()

You can use within group syntax

SELECT Color
    , Count(*) AS Count
    , STRING_AGG([Order],',') WITHIN GROUP (ORDER BY [Order]) AS AggOrder
FROM MyTable
GROUP BY Color

Oracle | Group by to fetch unique comma separated values

One option is to split skills into rows (the temp CTE), and then aggregate them back (line #11):

SQL> with temp as
  2    (select distinct
  3            student_id,
  4            trim(regexp_substr(skills, '[^,]+', 1, column_value)) skill
  5     from student_skills cross join
  6       table(cast(multiset(select level from dual
  7                           connect by level <= regexp_count(skills, ',') + 1
  8                          ) as sys.odcinumberlist))
  9    )
 10  select student_id,
 11         listagg(skill, ', ') within group (order by skill) skills
 12  from temp
 13  group by student_id;

STUDENT_ID SKILLS
---------- ------------------------------
       101 C, CPP, SQL
       102 CPP, Java, JavaScript

SQL>

SQL Server - join rows into comma separated list

You are missing the condition inside the sub query.

SELECT t2.Id, STUFF((SELECT ',' + CAST(VALUE AS varchar) FROM @MyTable t1  where t1.Id =t2.ID FOR XML PATH('')), 1 ,1, '') AS ValueList
FROM @MyTable t2
GROUP BY t2.Id

Demo

Convert multiple rows into one with comma as separator

This should work for you. Tested all the way back to SQL 2000.

create table #user (username varchar(25))

insert into #user (username) values ('Paul')
insert into #user (username) values ('John')
insert into #user (username) values ('Mary')

declare @tmp varchar(250)
SET @tmp = ''
select @tmp = @tmp + username + ', ' from #user

select SUBSTRING(@tmp, 0, LEN(@tmp))

---

Related Topics