We help IT Professionals succeed at work.

how to do a sql query in rows instead of columns

g_johnson
g_johnson asked
on
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?
Comment
Watch Question

Commented:
Use PIVOT

 http://archive.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=PIVOTData should give you some good examples to start with.
HainKurtSr. System Analyst

Commented:
use

when status='C' then 0 else 1 end

in your select query...
HainKurtSr. System Analyst

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

Author

Commented:
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,
HainKurtSr. System Analyst

Commented:
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
HainKurtSr. System Analyst

Commented:
if it is not available sql2000, check this sample

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