We help IT Professionals succeed at work.

Pivot Sql statement

cl5bc
cl5bc asked
on
Hi,

I have a table with the folowing structure
OrderDate      Total      Online
2/10/2007      1      Y
2/10/2007      1      N

Is it posible to convert it into the following in a single statement?
OrderDate      Online      OffLine
2/10/2007      1      1
Comment
Watch Question

Top Expert 2008
Commented:
try this code:

Select OrderDate, SUM(IIF(Online='Y',1,0)),SUM(IIF(Online='N',1,0))
FROM table Group BY OrderDate
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012
Commented:
you might want to use "total" instead of "1" in your IFF functions

or if you're using oracle, it would look like this...

select orderdate, sum(decode(online,'Y',total)) online, sum(decode(online,'N',total)) offline
from table
group by orderdate

Note, if you're using oracle 11g, pivot function is built in and would look like this....

select * from ordertable
pivot (sum(total) for on_line in ('Y' "Online", 'N' "Offline"))  
Top Expert 2008

Commented:
thanks for correcting me.

it should be total instead of 1.

Author

Commented:
I used the pivot function under 2005 in the end

PIVOT (
SUM(TOTAL) FOR ONLINEORDER IN ([N],[Y])

Explore More ContentExplore courses, solutions, and other research materials related to this topic.