How to use GROUP BY to concatenate strings in SQL Server?
No CURSOR, WHILE loop, or User-Defined Function needed.
Just need to be creative with FOR XML and PATH.
[Note: This solution only works on SQL 2005 and later. Original question didn't specify the version in use.]
CREATE TABLE #YourTable ([ID] INT, [Name] CHAR(1), [Value] INT)
INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (1,'A',4)
INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (1,'B',8)
INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (2,'C',9)
SELECT
[ID],
STUFF((
SELECT ', ' + [Name] + ':' + CAST([Value] AS VARCHAR(MAX))
FROM #YourTable
WHERE (ID = Results.ID)
FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
,1,2,'') AS NameValues
FROM #YourTable Results
GROUP BY ID
DROP TABLE #YourTable
How to use GROUP BY to concatenate strings in MySQL?
SELECT id, GROUP_CONCAT(name SEPARATOR ' ') FROM table GROUP BY id;
https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html#function_group-concat
From the link above, GROUP_CONCAT
: This function returns a string result with the concatenated non-NULL values from a group. It returns NULL if there are no non-NULL values.
How to use GROUP BY to concatenate strings while joining multiple tables?
You can specify a CTE – common table expression to store your temporary result :
with cteTbl ( CardID
, TechName
, problemReported ) as (
select j.CardID
, p.ProblemReported
, ( select TechnicianName
from easy_tbltechnicianMaster
where TechnicianID = t.technicianID ) as TechName
from easy_tbljobcard as j
join easy_technician as t on t.CardID = j.CardID
left join easy_tblproblem as p on p.CardID = t.CardID )
And then select
from it and concatenate all column values with the same t.techName
and t.CardID
in one row with for xml path('')
and after that replace the first comma ,
with stuff
:
select t.CardID
, t.TechName
, stuff( ( select ', ' + ProblemReported
from cteTbl
where TechName = t.TechName
order by ProblemReported
for xml path('') ), 1, 1, '') AS ProblemReported
from cteTbl t
group by t.TechName
, t.CardID
SQLFiddle
MS SQL - group by concat string
In sql server you can use FOR XML PATH
select SubscriberId,
categoriesAdded=Stuff((SELECT ',' + CAST(CategoryId as VARCHAR(255)) FROM catsToAdd t1 WHERE t1.SubscriberId=@categoriesToAdd.SubscriberId
FOR XML PATH (''))
, 1, 1, '' )
from @categoriesToAdd as catsToAdd
GROUP BY SubscriberId
Concatenate many rows into a single text string with grouping
try this -
SELECT DISTINCT
fileid
, STUFF((
SELECT N', ' + CAST([filename] AS VARCHAR(255))
FROM tblFile f2
WHERE f1.fileid = f2.fileid ---- string with grouping by fileid
FOR XML PATH (''), TYPE), 1, 2, '') AS FileNameString
FROM tblFile f1
SQL Server Concatenate GROUP BY
If you are using sql server 2005+. Then you can do like this:
SELECT
JobsTagMap.JobID,
STUFF
(
(
SELECT
',' +Title
FROM
Tags
WHERE
Tags.TagID=JobsTagMap.TagID
FOR XML PATH('')
)
,1,1,'') AS Title
FROM JobsTagMap
EDIT
Because you did not show us the table structure and the data in the different tables. It was a lite bit hard to know. So I assume that your table structure looks something like this:
CREATE TABLE JobsTagMap
(
JobID INT,
TagID INT
)
CREATE TABLE Tags
(
TagID INT,
Title VARCHAR(100)
)
With this data:
INSERT INTO JobsTagMap
VALUES(1,1),(1,2),(2,2),(2,4),(2,5)
INSERT INTO Tags
VALUES(1,'Tag1'),(2,'Tag2'),(3,'Tag2'),(4,'Tag5'),(5,'Tag9')
If you are getting that data that you are showing the JobID
cannot be unique. You might have the a Job
table somewhere where it is unique. If you just want to use these table that you are showing then you need to do something like this:
;WITH CTE
AS
(
SELECT
ROW_NUMBER() OVER(PARTITION BY JobID ORDER BY JobID) AS RowNbr,
JobsTagMap.*
FROM
JobsTagMap
)
SELECT
*,
STUFF
(
(
SELECT
',' +Title
FROM
Tags
JOIN JobsTagMap
ON Tags.TagID=JobsTagMap.TagID
WHERE
JobsTagMap.JobID=CTE.JobID
FOR XML PATH('')
)
,1,1,'') AS Title
FROM
CTE
WHERE
CTE.RowNbr=1
This will get you this result:
1 1 1 Tag1,Tag2
1 2 2 Tag2,Tag5,Tag9
So in the future always show what table structure and it data. That will give you better answers
How to concatenate text from multiple rows into a single text string in SQL Server
If you are on SQL Server 2017 or Azure, see Mathieu Renda answer.
I had a similar issue when I was trying to join two tables with one-to-many relationships. In SQL 2005 I found that XML PATH
method can handle the concatenation of the rows very easily.
If there is a table called STUDENTS
SubjectID StudentName
---------- -------------
1 Mary
1 John
1 Sam
2 Alaina
2 Edward
Result I expected was:
SubjectID StudentName
---------- -------------
1 Mary, John, Sam
2 Alaina, Edward
I used the following T-SQL
:
SELECT Main.SubjectID,
LEFT(Main.Students,Len(Main.Students)-1) As "Students"
FROM
(
SELECT DISTINCT ST2.SubjectID,
(
SELECT ST1.StudentName + ',' AS [text()]
FROM dbo.Students ST1
WHERE ST1.SubjectID = ST2.SubjectID
ORDER BY ST1.SubjectID
FOR XML PATH (''), TYPE
).value('text()[1]','nvarchar(max)') [Students]
FROM dbo.Students ST2
) [Main]
You can do the same thing in a more compact way if you can concat the commas at the beginning and use substring
to skip the first one so you don't need to do a sub-query:
SELECT DISTINCT ST2.SubjectID,
SUBSTRING(
(
SELECT ','+ST1.StudentName AS [text()]
FROM dbo.Students ST1
WHERE ST1.SubjectID = ST2.SubjectID
ORDER BY ST1.SubjectID
FOR XML PATH (''), TYPE
).value('text()[1]','nvarchar(max)'), 2, 1000) [Students]
FROM dbo.Students ST2
Group by in SQL Server and concatenate records in one column
You can use STUFF
and GROUP BY
with MIN,MAX,SUM
aggregation as below and in your example paid amount is in 5 digit
and amount in 6 digit
so it can't be zero
in balance. In my example I made 6 digits paid amount
to match with your expected result but you should correct and use it as needed
DECLARE @sales TABLE(company VARCHAR(50),
ref INT,
Tag INT,
event_date DATE,
sale_price INT,
Amount INT,
Receipt_No VARCHAR(50),
Paid INT,
Balance INT)
INSERT INTO @sales VALUES
('PRco Ltd',123,0311,'03-10-2018',610000,610000,'R19A0000761',500000,11000),
('PRco Ltd',123,0311,'03-10-2018',610000,610000,'R19A0000912',110000,0)
SELECT s.company,
s.ref,
s.Tag,
s.event_date,
MAX(s.sale_price) sale_price,
MAX(s.amount) amount,
MAX(s1.receipt) receipt,
SUM(s.paid) paid,
(MAX(s.amount)-SUM(s.paid)) balance
FROM @sales s
OUTER APPLY (
select stuff(
(select ',' + s1.Receipt_No
from @sales s1
where s1.company = s.company
AND s1.ref = s.ref
AND s1.Tag = s.Tag
AND s1.event_date = s.event_date
for xml path('')
)
, 1, 1, '') receipt
) s1
GROUP BY s.company, s.ref, s.Tag, s.event_date
OUTPUT:
company ref Tag event_date sale_price amount receipt paid balance
PRco Ltd 123 311 2018-03-10 610000 610000 R19A0000761,R19A0000912 610000 0
Related Topics
SQL Server - Best Way to Get Identity of Inserted Row
Split Comma Separated Values to Columns in Oracle
Select Top 10 Records For Each Category
Strange Duplicate Behavior from Group_Concat of Two Left Joins of Group_Bys
Multiple Insert Statements Vs. Single Insert With Multiple Values
Ordering by Specific Field Value First
How to Do 'Insert If Not Exists' in MySQL
How to Do an Update Statement With Join in SQL Server
Referring to a Column Alias in a Where Clause
How to Use Case Statement in a Join Condition
Join Tables With Sum Issue in MySQL
Find All Tables Containing Column With Specified Name - Ms SQL Server
Is There Any Function in Oracle Similar to Group_Concat in MySQL