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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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.
Go to Top of Page
   

- Advertisement -