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
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
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 : 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 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
How to select row/s from an other table using a comma-separated value from the first table?
SELECT t1.subject, GROUP_CONCAT(t2.subject)
FROM faculty t1
JOIN subjects t2 ON FIND_IN_SET(t2.code, t1.subject)
GROUP BY t1.subject;
https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=3e40ab353ff1e0a1cde678225fa63ed2
Related Topics
Cross Join Without Duplicate Combinations
Postgresql Wildcard Like for Any of a List of Words
How Exactly Does Using or in a MySQL Statement Differ With/Without Parentheses
MySQL #1140 - Mixing of Group Columns
Display Names of All Constraints for a Table in Oracle SQL
How to Execute an in Lookup in SQL Using Golang
Deferrable Constraints in SQL Server
How to Use Timestamp_To_Scn and Scn_To_Timestamp in Oracle
Coldfusion - Variable Field Name When Looping Through Database Query Results
Oracle: Combine Multiple Results in a Subquery into a Single Comma-Separated Value
How Do SQL Exists Statements Work
Split String by Delimiter Position Using Oracle SQL
How Does This Case Expression Reach the Else Clause
Automate Version Number Retrieval from .Dtsx Files
Adding a New SQL Column with a Default Value