SQL Server: Group by Clause to Get Comma-Separated Values

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

Group By clause with comma-separated values

You were almost there, all you need is an INNER JOIN onto the Chef table inside your STUFF statement and then you simply swap out ChefId with Name.

SELECT Dish, ChefId = 
    STUFF((SELECT ', ' + c1.Name
        FROM Specialty s1
           INNER JOIN Chef c1
             ON s1.ChefId = c1.Id
        WHERE s1.Dish = s2.Dish
        FOR XML PATH('')), 1, 2, '')
    FROM Specialty s2
    GROUP BY Dish

I have a working SQL Fiddle example here: http://www.sqlfiddle.com/#!6/9b7d5/6

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 view a column values in comma separated string with group by clause using SQL query?

You can use the STUFF() as shown below. Just replace cte with your original query. Another way is to create a temp table with your current result and replace that cte with the temp table newly created.

SELECT DoNum
        , CardCode
        , CardName
        , SipCode
        , SlpName
        , STUFF((SELECT DISTINCT ',' + CONVERT(VARCHAR,U_Product_Type) 
         FROM [cte] t1
         WHERE (DoNum = cte.DoNum
         and CardCode = t1.CardCode
         and CardName = t1.CardName
         and SipCode = t1.SipCode
         and SlpName = t1.SlpName) 
         FOR XML PATH ('')), 1, 2, '') AS U_Product_Type
         ,sum(LineTotal) as LineTotal
         ,sum(dwpnt) as dwpnt
FROM cte
group by DoNum
, CardCode
, CardName
, SipCode
, SlpName

Live Demo

SQL Server - SUM and comma-separated values using GROUP BY clause

It doesn't look like yo need to NDEvent table here at all (though I include it in the sample data). Just SUM and STRING_AGG against EventAppointment:

USE Sandbox
GO

WITH NDEvent AS(
    SELECT *
    FROM (VALUES(33,CONVERT(datetime,'2020-10-23T15:00:00.000')),
                (33,CONVERT(datetime,'2020-10-23T15:00:00.000')),
                (35,CONVERT(datetime,'2020-10-21T03:30:00.000')),
                (35,CONVERT(datetime,'2020-10-24T15:00:00.000')),
                (35,CONVERT(datetime,'2020-10-25T15:00:00.000')),
                (34,CONVERT(datetime,'2020-10-23T15:00:00.000')))V(EventID,EndTime)),
EventAppointment AS(
    SELECT *
    FROM (VALUES(1,7647 ,34,10.00),
                (2,7647 ,34,10.00),
                (3,28531,33,20.00),
                (4,7647 ,35,20.00),
                (5,7647 ,35,100.00),
                (6,7647 ,35,200.00))V(Id,DocId, EventID, Amount))
SELECT DocID,
       EventID,
       SUM(Amount) AS Amount,
       STRING_AGG(Id,',') WITHIN GROUP (ORDER BY Id) AS IDs
FROM EventAppointment EA
GROUP BY DocId,
         EventID;

SQL Server : query for comma separated with group by

For older version of SQL Server i would use FOR XML clause with STUFF() :

SELECT c.CountryId, c.CountryName,
       STUFF( (SELECT DISTINCT ' ,'+ct.CityName   
               FROM City ct
               WHERE ct.CountryId = c.CountryId 
               FOR XML PATH ('')
              ), 1, 1, ''
            ) AS CitiesNames
FROM Country c;

You can use STRING_AGG() for newer version (SQL Server 2017). However, DISTINCT is redundant inside STUFF() if you don't have a duplicate cities. So, you can remove it.

For better performance you need index Country (CountryId) include CountryName, CityName.

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

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

---

Related Topics