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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

crisco96Commented:
Use PIVOT

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.