November 22, 2013 at 2:17 am
Hi,
This is my table
CREATE TABLE [dbo].[marks_581](
[Name] [varchar](30) NOT NULL,
[Subject] [varchar](30) NOT NULL,
[Marks] [int] NOT NULL
)
INSERT INTO marks_581
select 'Dishant','English',40 union all
select 'Dishant','Maths',45 union all
select 'Dishant','Hindi',49 union all
select 'Pranay','English',41 union all
select 'Pranay','Maths',45 union all
select 'Pranay','Hindi',50 union all
select 'Gautham','English',41 union all
select 'Gautham','Maths',45 union all
select 'Gautham','Hindi',49
select * from marks_581
And my output should be like the below one
Name |Subject1|Marks1|Subject2|marks2|Subject3|marks3
Dishant|English |40 |Maths |45 |hindi |49
Gautham|English |40 |Maths |45 |hindi |49
Pranay |English |40 |Maths |45 |hindi |49
This is my query and I am not able to get the subject name in subjects column. It says like couldn't convert varchar to datatype int. Any anyone help me?
SELECT Name,
MAX(CASE WHEN Subject= 'English' THEN Subject ELSE 0 END) AS Subject1,
SUM(CASE WHEN Subject= 'English' THEN Marks ELSE 0 END) AS Marks1,
MAX(CASE WHEN Subject= 'Maths' THEN Subject ELSE 0 END) AS Subject2,
SUM(CASE WHEN Subject= 'Maths' THEN Marks ELSE 0 END) AS Marks2,
MAX(CASE WHEN Subject= 'Hindi' THEN Subject ELSE 0 END) AS Subject3,
SUM(CASE WHEN Subject= 'Hindi' THEN Marks ELSE 0 END) AS Marks3
FROM marks_581
GROUP BY Name
ORDER BY Name
November 22, 2013 at 2:48 am
RESOLVED IT MYSELF
SELECT Name,
MAX(CASE WHEN Subject= 'English' THEN Subject END) AS Subject1,
SUM(CASE WHEN Subject= 'English' THEN Marks ELSE 0 END) AS Marks1,
MAX(CASE WHEN Subject= 'Maths' THEN Subject END) AS Subject2,
SUM(CASE WHEN Subject= 'Maths' THEN Marks ELSE 0 END) AS Marks2,
MAX(CASE WHEN Subject= 'Hindi' THEN Subject END) AS Subject3,
SUM(CASE WHEN Subject= 'Hindi' THEN Marks ELSE 0 END) AS Marks3
FROM marks_581
GROUP BY Name
ORDER BY Name
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply