asked on

how to do a sql query in rows instead of columns

I have a table called orders. One field is "status" and can be of the values C,1,4,5,7,8,9
Another field is tot_dollars (amount)
I want to derive and indicator based on status in that if status is C the value is zero else the value is 1
I want to return an order count per status

I need to present my data like this
status C 1 4 5 7 8 9
OCount ?? ?? ?? ?? ?? ?? ?? <-- whatever it turns out to be
OAmt $$ $$ $$ $$ $$ $$ $$
Ind 0 1 1 1 1 1 1

Can this be done with a sql query?

ASKER CERTIFIED SOLUTION

crisco96

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

HainKurt

use

when status='C' then 0 else 1 end

in your select query...

HainKurt

please post table structure, a sample data, and resultset you are looking for...

g_johnson

ASKER

table structure:

ord_no char(8), status char(1), order amount decimal (13,2)

sample data
00000001 C 100.00
00000002 7 150.00
00000003 8 100.00
00000004 1 200.00
00000005 C 300.00
00000006 1 250.00

desired results
C 1 7 8 <-- status
2 2 1 1 <-- order count
400.00 450.00 150.00 100.00 <-- order value
0 1 1 1 ,-- Indicator

Unfortunately, assume SQL2000 if you can.

Thanks,

HainKurt

do you really need this? can you do this in excel or in your app?
you can try using pivoting in t-sql but it may be difficult to write it...

have a look at this

http://msdn.microsoft.com/en-us/library/ms177410.aspx

HainKurt

if it is not available sql2000, check this sample

http://stackoverflow.com/questions/312861/pivot-using-sql-server-2000