group by date - but across page

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 2000 Forums

SQL Server Development (2000)

group by date - but across page

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-09-02 : 06:53:26

Christiaan writes "Hi,
To count according to months I use:

select datepart(mm,datecaptured),count(*)
from member
group by datepart(mm,datecaptured)

How can I display the result - across the page in columns instead of in rows

Example - if the returned result of above stored proc. returns:

1, 23
2, 42334
3,31

I would like it to return

Jan Feb Mar
23 42334 31

Thanks for your time
Regards
Christiaan"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-02 : 10:31:16

that is a reporting/presentation issue. How do you wish to present the results? (please do not say in Query Analyzer)

Let us know, and we can tell you the best way. Presentation and formatting should not done using SQL server.

- Jeff

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2003-09-02 : 10:44:02

True this is ideally handled at the presentation layer, but I have had many instances where it is useful to produce these in SQL especially since it can handle them very nicely.

SELECT	
	  SUM(CASE WHEN datepart(mm, datecaptureed) = 1 THEN 1 ELSE 0 END) as 'Jan'
	, SUM(CASE WHEN datepart(mm, datecaptureed) = 2 THEN 1 ELSE 0 END) as 'Feb'
	, SUM(CASE WHEN datepart(mm, datecaptureed) = 3 THEN 1 ELSE 0 END) as 'Mar'
	, SUM(CASE WHEN datepart(mm, datecaptureed) = 4 THEN 1 ELSE 0 END) as 'Apr'
	, SUM(CASE WHEN datepart(mm, datecaptureed) = 5 THEN 1 ELSE 0 END) as 'May'
	, SUM(CASE WHEN datepart(mm, datecaptureed) = 6 THEN 1 ELSE 0 END) as 'Jun'
	, SUM(CASE WHEN datepart(mm, datecaptureed) = 7 THEN 1 ELSE 0 END) as 'Jul'
	, SUM(CASE WHEN datepart(mm, datecaptureed) = 8 THEN 1 ELSE 0 END) as 'Aug'
	, SUM(CASE WHEN datepart(mm, datecaptureed) = 9 THEN 1 ELSE 0 END) as 'Sep'
	, SUM(CASE WHEN datepart(mm, datecaptureed) = 10 THEN 1 ELSE 0 END) as 'Oct'
	, SUM(CASE WHEN datepart(mm, datecaptureed) = 11 THEN 1 ELSE 0 END) as 'Nov'
	, SUM(CASE WHEN datepart(mm, datecaptureed) = 12 THEN 1 ELSE 0 END) as 'Dec'

FROM	member

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources