Summarize a result set in a different format

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

General SQL Server Forums

New to SQL Server Programming

Summarize a result set in a different format

Author

Topic

anuradavijay13
Starting Member

1 Post

Posted - 2010-12-09 : 20:34:13

Hi, my table structure is as follows:

id int, segment char (1), responses int, checked datetime
1,'A',0,'2009-05-01'
2,'B',1,'2009-05-01'
3,'C',0,'2009-05-01'
4,'A',0,'2009-05-02'
5,'B',0,'2009-05-02'
6,'C',5,'2009-05-02'

I have to print that out in the following format:
2009-05-01,0,1,0
2009,05,02,0,0,5

(where the numbers 0,1,0 correspond to a,b,c for that day.)

i'm new to result-set traversal but have an idea that I should be using a cursor. Can anyone help?

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-12-09 : 22:54:45

TRY:

Declare @Test table
(
id int,
segment char (1),
responses int,
checked datetime
)

Insert into @Test
select 1,'A',0,'2009-05-01' union
select 2,'B',1,'2009-05-01' union
select 3,'C',0,'2009-05-01' union
select 4,'A',0,'2009-05-02' union
select 5,'B',0,'2009-05-02' union
select 6,'C',5,'2009-05-02'

SELECT DISTINCT s1.checked,
STUFF((SELECT ',' + cast(s2.responses as varchar(100)) FROM @Test AS s2 WHERE s2.checked = s1.checked FOR XML PATH('')), 1, 1, '') AS responses
FROM @Test AS s1
ORDER BY s1.checked

Regards,
Bohra

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-12-11 : 01:07:59

[code]SELECT checked,
MAX(CASE WHEN segment = 'A' THEN responses ELSE NULL END) AS A,
MAX(CASE WHEN segment = 'B' THEN responses ELSE NULL END) AS B,
MAX(CASE WHEN segment = 'C' THEN responses ELSE NULL END) AS C
FROM table
GROUP BY checked
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources