SQL comma-separated row with Group By clause

Question

I have the following query:

SELECT
  Account,
  Unit,
  SUM(state_fee),
  Code
FROM tblMta
WHERE MTA.Id = '123'
GROUP BY Account,Unit

This of course throws an exception because the Code is not in the group by clause. Each state_fee has a code. How do I get this code to display in 1 record (1 code per state_fee which is multiple state_fee per unit) as a comma-separated list? I looked into different solutions on here but I couldn't find any that worked with a group by.

score 23 · Accepted Answer · edited May 23 '17 at 10:30

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:

score 0 · Answer 2 · answered Jan 13 '14 at 17:50

This will show you the table, index name, index type, indexed columns, and included columns:

with [indexes] (table_name, index_name, column_name, index_id, key_ordinal, object_id, type_desc)
as(
SELECT  distinct
  T.[name] AS [table_name], I.[name] AS [index_name], 
  AC.[name] AS [column_name],
  I.[index_id], IC.[key_ordinal], T.[object_id], i.type_desc
FROM sys.[tables] AS T  
  INNER JOIN sys.[indexes] I ON T.[object_id] = I.[object_id]  
  INNER JOIN sys.[index_columns] IC ON I.[object_id] = IC.[object_id]  and IC.index_id=I.index_id
  LEFT OUTER JOIN sys.[all_columns] AC ON T.[object_id] = AC.[object_id] AND IC.[column_id] = AC.[column_id] 
WHERE T.[is_ms_shipped] = 0 AND I.[type_desc] <> 'HEAP'
)
select
    distinct
    db_name() as dbname,
    type_desc,
    table_name,
    index_name,
    column_name,
    STUFF((
        select ', ' + column_name
        from [indexes] t2
        where t1.table_name=t2.table_name and t1.[index_name]=t2.[index_name] and t2.[key_ordinal] = 0
        for xml path('')), 1, 2, '') inc_cols
from [indexes] t1
where t1.[key_ordinal] = 1
GROUP BY table_name, index_name, type_desc, column_name

score 0 · Answer 3 · answered Jul 06 '22 at 09:27

In SQL Server 2017 (14.x) and later you can use the STRING_AGG function:

https://learn.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-ver16

SELECT
  Account,
  Unit,
  SUM(state_fee),
  STRING_AGG(Code, ',')
FROM tblMta
WHERE MTA.Id = '123'
GROUP BY Account,Unit

Depending on the data type of Code you might need to convert it:

SELECT
  Account,
  Unit,
  SUM(state_fee),
  STRING_AGG(CONVERT(NVARCHAR(max), Code), ',')
FROM tblMta
WHERE MTA.Id = '123'
GROUP BY Account,Unit

score 0 · Answer 4 · edited Aug 29 '13 at 17:16

0

There is no built-in aggregate function to concatenate, but this article discusses several alternative solutions, including a user-defined concatenate aggregate function:

https://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/

edited Aug 29 '13 at 17:16

Martin Smith

438,706
87
741
845

answered Sep 16 '11 at 18:16

BlueMonkMN

25,079
9
80
146

First I thought projectdms simply scrapes the original article w/o attribution, but actually the site belongs to the article author – Remus Rusanu Sep 16 '11 at 18:24
The link is dead. Here is an alternative: https://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/ – gpinkas May 22 '13 at 14:13

SQL comma-separated row with Group By clause

4 Answers4

Linked

Related