Rows to Columns

[dbljackson]

I have a table that keeps sales by day.

I want to return the results in column fashion. Similar to Cross-Tab.

Example

Select sum(Sales on Mondays), sum(Sales on Tuesdays), ...
where date between ( xxxx) and (xxxx)

Any suggestions most appreciated.

TIA

 

[grega]

What DB are you using? I could do it in Sybase (and therefore probably MS SQL Server)

Greg.

 

[grega]

Out of interest, if you have a table sales with 2 columns sales_date and sales_qty, then this will work in Sybase (and probably can be adapted for other servers) ....

select sum(case when datepart(dw,sales_date)=2 then sales_qty else 0 end) Monday
,
sum(case when datepart(dw,sales_date)=3 then sales_qty else 0 end) Tuesday,
sum(case when datepart(dw,sales_date)=4 then sales_qty else 0 end) Wednesday,
sum(case when datepart(dw,sales_date)=5 then sales_qty else 0 end) Thursday,
sum(case when datepart(dw,sales_date)=6 then sales_qty else 0 end) Friday,
sum(case when datepart(dw,sales_date)=0 then sales_qty else 0 end) Saturday,
sum(case when datepart(dw,sales_date)=1 then sales_qty else 0 end) Sunday
from sales

Greg.

 

[dbljackson]

Thanks Greg.

Just what I needed.

BTW I'm using SQL Server.