SQL Server Concatenate Group By

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

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

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

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

Concatenate values that are grouped by a column

You have nothing linking your inner and outer references to [Table], and you also need to make the outer reference distinct. Finally you need to either have no column name within your subquery, or it needs to be [text()]

SELECT  [Code]
        ,[Ref]
        ,STUFF((SELECT DISTINCT [Value] AS [text()]
                FROM [Table] AS T2
                WHERE T1.Code = T2.Code -- LINK HERE
                AND T2.Ref = T2.Ref     -- AND HERE
                FOR XML PATH ('')
                ),1, 1,'') AS [Values]
FROM    [Table] AS T1
GROUP BY T1.Code, T1.Ref; -- GROUP BY HERE

As an aside, you do not need to use STUFF as you have no delimiter, STUFF is typically used to remove the chosen delimiter from the start of the string. So when you have a string like ,value1,value2,value3, STUFF(string, 1, 1, '') will replace the first character with '' leaving you with value1,value2,value3.

You should also use the value xquery method to ensure you are not tripped up by special characters, if you don't and you try an concatenate ">>" and "<<" you would not end up with ">><<" as you might want, you would get ">><<", so a better query would be:

SELECT  t1.Code,
        t1.Ref,
        [Values] = (SELECT  DISTINCT [text()] = [Value]
                    FROM    [Table] AS t2
                    WHERE   T1.Code = T2.Code
                    AND     T2.Ref = T2.Ref
                    FOR XML PATH (''), TYPE
                ).value('.', 'NVARCHAR(MAX)')
FROM    [Table] AS T1
GROUP BY t1.Code, t1.Ref;

ADDENDUM

Based on the latest edit to the question it appears as though your Value column is coming from another table, linked to the first table by Code. If anything this makes your query simpler. You don't need the JOIN, but you still need to make sure that there is an expression to link the outer table to the inner table your subquery. I am assuming that the rows are unique in the first table, so you probably don't need the group by either:

SELECT  t1.Code,
        t1.Ref,
        [Values] = (SELECT  DISTINCT [text()] = t2.[Value]
                    FROM    [Table2] AS t2
                    WHERE   T1.Code = T2.Code
                    FOR XML PATH (''), TYPE
                ).value('.', 'NVARCHAR(MAX)')
FROM    [Table] AS T1;

WORKING EXAMPLE

CREATE TABLE #Table1 (Code CHAR(2), Ref VARCHAR(10));
INSERT #Table1 VALUES ('A1', 'Car'), ('B2', 'Truck'), ('C3', 'Van');

CREATE TABLE #Table2 (Code CHAR(2), Value VARCHAR(2));
INSERT #Table2
VALUES ('A1', 'A'), ('A1', '-'), ('A1', 'B'),
        ('B2', 'CC'), ('B2', 'D'), ('B2', '-'),
        ('C3', 'F'), ('C3', '-'), ('C3', 'G');

SELECT  t1.Code,
        t1.Ref,
        [Values] = (SELECT  DISTINCT [text()] = t2.[Value]
                    FROM    #Table2 AS t2
                    WHERE   T1.Code = T2.Code
                    FOR XML PATH (''), TYPE
                ).value('.', 'NVARCHAR(MAX)')
FROM    #Table1 AS T1;

SQL Server Concatenate Multiple Rows By Group Using FOR XML PATH

You need a correlated subquery:

SELECT hs.department AS department,
       STUFF( (SELECT ', ' + top_employee
               FROM findhighest_secondstep hs2
               WHERE hs2.department = hs.department
               FOR XML PATH('')
              ), 1, 2, ''
            ) as top_employees                   
FROM findhighest_secondstep hs
GROUP BY hs.department

GROUP BY to combine/concat a column

SELECT
     [User], Activity,
     STUFF(
         (SELECT DISTINCT ',' + PageURL
          FROM TableName
          WHERE [User] = a.[User] AND Activity = a.Activity
          FOR XML PATH (''))
          , 1, 1, '')  AS URLList
FROM TableName AS a
GROUP BY [User], Activity

SQLFiddle Demo

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