August 18, 2008 at 12:27 pm
i have a table t1 with column names ---status,method
so i have
method --status
bank------success
bank-----failure
bank---success
finance----success
finance----sucess
finance---success
loan-----failure---
loan---failure
but now i need to get the output in such a way that ...
method----success---failure---
--total
bank -----2---------------1------------3
loan------0----------------2------------2
finance----3-------------0-------------3
I need to write a stored proc for this plz let me know .....
--
i am using sql server 2000...
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
August 18, 2008 at 12:56 pm
Rack,
If you need a SQL 2000 solution you should post in SQL 2000 forum. Many posters, like myself, will base our answers on the forum we are in.
Here you go:
[font="Courier New"]DECLARE @table TABLE (method VARCHAR(10), stat VARCHAR(10))
INSERT INTO @table
SELECT
'bank', 'success'
UNION ALL
SELECT
'bank', 'failure'
UNION ALL
SELECT
'bank','success'
UNION ALL
SELECT
'finance','success'
UNION ALL
SELECT
'finance','success'
UNION ALL
SELECT
'finance','success'
UNION ALL
SELECT
'loan','failure'
UNION ALL
SELECT
'loan','failure'
SELECT
method,
SUM(CASE
WHEN stat = 'success' THEN 1
ELSE 0
END) AS successes,
SUM(CASE
WHEN stat = 'failure' THEN 1
ELSE 0
END) AS failures,
COUNT(stat) AS total
FROM
@table
GROUP BY
method[/font]
Check out my blog post on this method as well. http://wiseman-wiseguy.blogspot.com/2008/07/aggregation-based-on-date.html
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 19, 2008 at 5:29 am
I agree with Jack that you should post the question in the right forum only.
Coming to your question, a more performant way can be as follows:
select method, sum(case when success = 'success' then 1 else 0 end) success,
sum(case when success = 'failure' then 1 else 0 end) failure from x
group by method
August 19, 2008 at 5:34 am
Arjun,
Thanks fro confirming what i had posted.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply