15,942,757 members
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

## Solution 1

SELECT ID,SUM(SCORE)'Score',CASE WHEN ID=1 THEN 'Excellent' when ID=2 THEN 'Good' END 'Performance' FROM ID_SCORE GROUP BY ID

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

## Solution 2

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 ```

v2
ankur789 7-Mar-13 0:08am
sir i donot understand this

## Solution 3

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'```

ankur789 7-Mar-13 0:32am
error shows incorrect syntax near with
Davidduraisamy 7-Mar-13 0:41am
wats the problem here its working