GROUP BY with containing Id's - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

GROUP BY with containing Id's

Author

Topic

samiko
Starting Member

2 Posts

Posted - 2014-02-27 : 13:46:12

Hi,

Is there a way to add a col with delimitered containing Id's of a particular GROUP BY clause?

For example:

ID. AMOUNT. SOME_COL
1. 10. 'A'
2. 20. 'B'
3. 20. 'A'

SELECT SUM(AMOUNT) AS TOTAL, SOME_COL...
FROM TBL
GROUP BY SOME_COL

RESULT should be:

TOTAL. SOME_COL. IDS.
30. 'A' '1,3'
20. 'B' '2'

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-02-27 : 13:52:57

One of the popular methods is to use the FOR XML PATH:
http://stackoverflow.com/questions/14560956/sql-server-2008-concatenating-strings

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-02-27 : 15:11:42

[code]DECLARE @Sample TABLE
(
ID INT,
AMOUNT INT,
SOME_COL CHAR(1)
);

INSERT @Sample
(
ID,
AMOUNT,
SOME_COL
)
VALUES (1, 10, 'A'),
(2, 20, 'B'),
(3, 20, 'A');

SELECT d.TOTAL,
d.SOME_COL,
STUFF(f.Data, 1, 2, '') AS IDs
FROM (
SELECT SOME_COL,
SUM(AMOUNT) AS TOTAL
FROM @Sample
GROUP BY SOME_COL
) AS d
CROSS APPLY (
SELECT ', ' + CAST(x.ID AS VARCHAR(12))
FROM @Sample AS x
WHERE x.SOME_COL = d.SOME_COL
FOR XML PATH('')
) AS f(Data);[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

samiko
Starting Member

2 Posts

Posted - 2014-02-28 : 00:00:52

Thanks for your reply. I have tried it, but its quite slow on performance

samiko
Starting Member

2 Posts

Posted - 2014-02-28 : 00:01:23

Thanks for your reply. I have tried it, but its quite slow on performance

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-02-28 : 11:21:03

Add proper indexes.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

sqlsaga
Yak Posting Veteran

93 Posts

Posted - 2014-02-28 : 11:48:09

Hi samiko, try below code..



DECLARE @TABLE TABLE
(
ID INT,
AMOUNT INT, 
SOME_COL CHAR(1)
)

INSERT INTO @TABLE VALUES (1, 10, 'A'), (2, 20, 'B'), (3, 20, 'A')

;WITH CTE AS
(
SELECT DISTINCT SOME_COL, AMOUNT, 
		STUFF((SELECT ','+CAST(ID AS VARCHAR(10)) FROM @TABLE S2 WHERE S1.Some_Col = S2.SOME_COL FOR XML PATH('')),1,1,'') AS ID
FROM @TABLE S1
)
SELECT ID, SOME_COL, SUM(Amount) AS Amount
FROM CTE
GROUP BY SOME_COL, ID

Good Luck :) .. visit [url]www.sqlsaga.com[/url] for more t-sql code snippets and BI related how to articles.

Visit www.sqlsaga.com for more t-sql snippets and BI related how to's.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources