help with a query - 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)

help with a query

Author

Topic

xpandre
Posting Yak Master

212 Posts

Posted - 2014-08-14 : 22:17:51

Hi,
I have a table with below structure and data

Servicename PageName ErrorCount
Service1 Page1 5
Service1 Page2 2
Service1 Page3 2
Service1 Page1 6
Service1 Page3 5
Service2 Page1 4
Service2 Page3 2

need output like:
service totalcount pages
service1 20 page1, page2, page3
service2 6 page1, page3

Thanks
Sam

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-08-15 : 00:22:25

[code]DECLARE @Sample TABLE
(
ServiceName VARCHAR(20) NOT NULL,
PageName VARCHAR(20) NOT NULL,
ErrorCount INT NOT NULL
);

INSERT @Sample
(
Servicename,
PageName,
ErrorCount
)
VALUES ('Service1', 'Page1', 5),
('Service1', 'Page2', 2),
('Service1', 'Page3', 2),
('Service1', 'Page1', 6),
('Service1', 'Page3', 5),
('Service3', '<1>', 5),
('Service2', 'Page1', 4),
('Service2', 'Page3', 2);

-- SwePeso
SELECT sn.ServiceName AS [Service],
sn.ErrorCount AS TotalCount,
STUFF(f.Data.value('(.)', 'VARCHAR(MAX)'), 1, 2, '') AS Pages
FROM (
SELECT ServiceName,
SUM(ErrorCount) AS ErrorCount
FROM @Sample
GROUP BY ServiceName
) AS sn
CROSS APPLY (
SELECT ', ' + x.PageName
FROM @Sample AS x
WHERE x.ServiceName = sn.ServiceName
GROUP BY x.PageName
ORDER BY x.PageName
FOR XML PATH(''),
TYPE
) AS f(Data);[/code]

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

xpandre
Posting Yak Master

212 Posts

Posted - 2014-08-15 : 17:48:41

Thank you!!!!

I had done it with nested CTE's, but this one is so compact and simpler!! Thank you!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources