Concat Groups in SQL Server

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

In SQL Server, how to concat -- group by using the concat column

You need to specify the full calculation for the GROUP BY as well as for the SELECT. This is because GROUP BY is logically considered before SELECT, so cannot access those calculations.

You could do it like this (table_name is not necessary because it's purely computed):

SELECT
    'dbo.our_table' as table_name,
    CONCAT(col1, '-', col2, '-', col3) as table_id,
    COUNT(*) as ct 
FROM dbo.our_table
group by CONCAT(col1, '-', col2, '-', col3)
order by ct desc;

But much better is to place calculations in a CROSS APPLY, this means it is accessible later by name as you wished:

SELECT
    'dbo.our_table' as table_name,
    v.table_id,
    COUNT(*) as ct 
FROM dbo.our_table
CROSS APPLY (VALUES (CONCAT(col1, '-', col2, '-', col3) ) ) as v(table_id)
group by v.table_id
order by ct desc;

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

Concat groups in SQL Server

For a clean and efficient solution you can create an user defined aggregate function, there is even an example that does just what you need.

You can then use it like any other aggregate function (with a standard query plan):

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

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

How to make a query with group_concat in sql server

Query:

SELECT
      m.maskid
    , m.maskname
    , m.schoolid
    , s.schoolname
    , maskdetail = STUFF((
          SELECT ',' + md.maskdetail
          FROM dbo.maskdetails md
          WHERE m.maskid = md.maskid
          FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
FROM dbo.tblmask m
JOIN dbo.school s ON s.ID = m.schoolid
ORDER BY m.maskname

Additional information:

String Aggregation in the World of SQL Server

How to do a CONCAT on a GROUP BY in SQL Server?

Use the below script using 'STUFF"

SELECT  ID [Student ID]
       ,STUFF((SELECT ', ' + CAST(COURSE_ID AS VARCHAR(10)) [text()]
         FROM student_course 
         WHERE STUDENT_ID =s.ID
         FOR XML PATH(''), TYPE)
        .value('.','NVARCHAR(MAX)'),1,2,' ') [Course Id]
FROM student s
GROUP BY ID

Here comes another method using 'Cross Apply'.

SELECT  Id as [Student ID],[Course ID]
FROM    (
        SELECT  [Id]
        FROM    student
        ) a
CROSS APPLY
        (
        SELECT  CASE ROW_NUMBER() OVER(ORDER BY COURSE_ID) WHEN 1 THEN '' ELSE ', ' END +CAST(COURSE_ID as varchar(50))
        FROM    student_course b
        WHERE   a.id = b.STUDENT_ID
        ORDER BY COURSE_ID
        FOR XML PATH ('')
        ) b([Course ID])

---

Related Topics