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?
LVL 4
g_johnsonAsked:
Who is Participating?
 
crisco96Commented:
Use PIVOT

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

when status='C' then 0 else 1 end

in your select query...
0
 
HainKurtSr. System AnalystCommented:
please post table structure, a sample data, and resultset you are looking for...
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
g_johnsonAuthor 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,
0
 
HainKurtSr. System AnalystCommented:
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
0
 
HainKurtSr. System AnalystCommented:
if it is not available sql2000, check this sample

http://stackoverflow.com/questions/312861/pivot-using-sql-server-2000
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.