Group by SQL Query on Comma Joined Column

Group by sql query on comma joined column

String splitting is faster using only CHARINDEX without XML or CTE.

Sample table

create table #tmp ([Data] int, [Mail] varchar(200))
insert #tmp SELECT 1,'m1@gmail.com,m2@hotmail.com,other, longer@test, fifth'
UNION ALL   SELECT 2,'m2@hotmail.com,m3@test.com'
UNION ALL   SELECT 3,'m3@single.com'
UNION ALL   SELECT 4,''
UNION ALL   SELECT 5,null

The query

select single, count(*) [Count]
from
(
    select ltrim(rtrim(substring(t.mail, v.number+1,
        isnull(nullif(charindex(',',t.mail,v.number+1),0)-v.number-1,200)))) single
    from #tmp t
    inner join master..spt_values v on v.type='p'
        and v.number <= len(t.Mail)
        and (substring(t.mail,v.number,1) = ',' or v.number=0)
) X
group by single

The only parts you supply are

• #tmp: your table name
• #mail: the column name

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

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 : 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 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

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

---

Related Topics