Microsoft SQL Server forum - Show All Months in First Column of Stored Procedure

Author

Show All Months in First Column of Stored Procedure

paulmac106@hotmail.com

2005-08-25, 8:24 pm

Hi,

I need what would be similar to a cross tab query in Access.

First Column down needs to show all the months, column headings would
be the day of the month....

1 2 3 4 etc...
Jan
Feb
Mar
etc

how do i set this up in a stored procedure?

any help to get me in the right direction would be greatly
appreciated!!

thanks,
paul

Chandra

2005-08-26, 3:23 am

hi paul
you do not have that flexibility. U can try it as:
select 'Jan' as ' ','' as [1], '' as [2], '' as [3]
union
select 'Feb' as ' ','' as [1], '' as [2], '' as [3]
union
select 'Mar' as ' ','' as [1], '' as [2], '' as [3]

best Regards,
Chandra
http://www.SQLResource.com/
http://chanduas.blogspot.com/
---------------------------------------

*** Sent via Developersdex http://www.droptable.com ***

jsfromynr

2005-08-26, 3:23 am

Hi Paul,
See If this can Help You.

Use NortWind
Create View vwMonths
as
Select DateName(month,dateA
dd(mm,MonthId,'20000
101'))
MonthName,(MonthId+1
) Mon From
(
Select 0 MonthId
Union
Select 1
Union
Select 2
Union
Select 3
Union
Select 4
Union
Select 5
Union
Select 6
Union
Select 7
Union
Select 8
Union
Select 9
Union
Select 10
Union
Select 11
) Cal

Select MonthName,
Max(Case When day(OrderDate) = 1 And Month(OrderDate)=Mon
Then
CustomerId End) 'Day 1',
Max(Case When day(OrderDate) = 2 And Month(OrderDate)=Mon
Then
CustomerId End) 'Day 2',
Max(Case When day(OrderDate) = 3 And Month(OrderDate)=Mon
Then
CustomerId End) 'Day 3',
Max(Case When day(OrderDate) = 4 And Month(OrderDate)=Mon
Then
CustomerId End) 'Day 4',
Max(Case When day(OrderDate) = 5 And Month(OrderDate)=Mon
Then
CustomerId End) 'Day 5',
Max(Case When day(OrderDate) = 6 And Month(OrderDate)=Mon
Then
CustomerId End) 'Day 6'
>From Orders,vwMonths Group by MonthName,Mon

With Warm regards
Jatinder Singh

Sponsored Links