Click here to Skip to main content
15,942,757 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Dear sir/ma'am


I am using sql enterprise 2000

table name value

table is

id score
1 10
1 20
2 5
2 10

sir i want the result like this
excellent is the sum of score of id 1 and good is the sum of score of id 2

excelent good
30 15
Posted

SELECT ID,SUM(SCORE)'Score',CASE WHEN ID=1 THEN 'Excellent' when ID=2 THEN 'Good' END 'Performance' FROM ID_SCORE GROUP BY ID
 
Share this answer
 
Comments
ankur789 6-Mar-13 23:41pm    
it gives result like
id score performance
1 30 excellent
2 15 good

But i want sir
excelent good
30 15

sir it is just like row to column format
Davidduraisamy 6-Mar-13 23:47pm    
You have oly this four record or having more record
ankur789 6-Mar-13 23:53pm    
only these four type record
at id 1=excelent
at id 2=good
at id 3=average
at id 4=poor

i want to answer such like
Excellent good Average Poor
Here you go!!
SQL
DECLARE @t TABLE (Id INT, Score INT)
INSERT INTO @t
SELECT 1,10
UNION ALL
SELECT 1,20
UNION ALL
SELECT 2,5
UNION ALL
SELECT 2,10
       
       
SELECT MAX(CASE WHEN t.Perf = 'Excellent' THEN t.Score ELSE NULL END) AS Excellent,
       MAX(CASE WHEN t.Perf = 'Good' THEN t.Score ELSE NULL END) AS Good
FROM   (
           SELECT CASE 
                       WHEN Id = 1 THEN 'Excellent'
                       WHEN Id = 2 THEN 'Good'
                  END       AS Perf,
                  SUM(Score )  AS Score 
           FROM   @t
           GROUP BY
                  Id
       ) AS t 
 
Share this answer
 
v2
Comments
ankur789 7-Mar-13 0:08am    
sir i donot understand this
SQL
WITH UserDetails AS
(
SELECT ID,SUM(SCORE)'SCORE' FROM ID_SCORE GROUP BY ID
)
SELECT (SELECT SCORE FROM UserDetails WHERE ID=1)'EXCELLENT',(SELECT SCORE FROM UserDetails WHERE ID=2)'GOOD'
 
Share this answer
 
Comments
ankur789 7-Mar-13 0:32am    
error shows incorrect syntax near with
Davidduraisamy 7-Mar-13 0:41am    
wats the problem here its working

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900