CODEWITHSUNDEEP
sqlsql-serversql-server-2008
user3003821
user3003821

Reputation:

Small change in SQL Query

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,       
    SUM(CASE WHEN Subject= 'English' THEN Subject ELSE 0 END) AS Subject1,
    SUM(CASE WHEN Subject= 'English' THEN Marks ELSE 0 END) AS Marks1,        
    SUM(CASE WHEN Subject= 'Maths' THEN Subject ELSE 0 END) AS Subject2,
    SUM(CASE WHEN Subject= 'Maths' THEN Marks ELSE 0 END) AS Marks2,    
    SUM(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

Upvotes: 0

Views: 81

Answers (5)

Pranay Rana
Pranay Rana

Reputation: 176956

SELECT Name,       
    MAX(CASE WHEN Subject= 'English' THEN Subject ELSE Subject  END) AS Subject1,
    SUM(CASE WHEN Subject= 'English' THEN Marks ELSE 0 END) AS Marks1,        
    MAX(CASE WHEN Subject= 'Maths' THEN Subject ELSE Subject  END) AS Subject2,
    SUM(CASE WHEN Subject= 'Maths' THEN Marks ELSE 0 END) AS Marks2,    
    MAX(CASE WHEN Subject= 'Hindi' THEN Subject ELSE 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

Upvotes: 1

Girish Sakhare
Girish Sakhare

Reputation: 763

SELECT Name,       
'English' AS Subject1,
SUM(CASE WHEN Subject= 'English' THEN Marks ELSE 0 END) AS Marks1,        
'Maths' AS Subject2,
SUM(CASE WHEN Subject= 'Maths' THEN Marks ELSE 0 END) AS Marks2,    
'Hindi' AS Subject3,
SUM(CASE WHEN Subject= 'Hindi' THEN Marks ELSE 0 END) AS Marks3 
FROM marks_581
GROUP BY Name
ORDER BY Name

Upvotes: 0

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239814

Use a different aggregate function, such as MAX(), for non-numeric data:

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

Although, given the nature of this query, we know we can simplify it further:

SELECT Name,       
    'English' AS Subject1,
    SUM(CASE WHEN Subject= 'English' THEN Marks ELSE 0 END) AS Marks1,        
    'Maths' AS Subject2,
    SUM(CASE WHEN Subject= 'Maths' THEN Marks ELSE 0 END) AS Marks2,    
    'Hindi' AS Subject3,
    SUM(CASE WHEN Subject= 'Hindi' THEN Marks ELSE 0 END) AS Marks3 
FROM marks_581
GROUP BY Name
ORDER BY Name

Upvotes: 2

Vinay Shukla
Vinay Shukla

Reputation: 1844

Hey what i feel is the mistake is here 

CREATE TABLE [dbo].[marks_581](
[Name] [varchar](30) NOT NULL,
[Subject] [varchar](30) NOT NULL,
[Marks] [int] NOT NULL
)

The table created using above commands will not have sub1 sub2 sub3 and mark1 mark2 and mark3

Upvotes: 0

PrfctByDsgn
PrfctByDsgn

Reputation: 1050

it's because of ...

 THEN Subject ELSE 0 END

... subject is varchar not int

Upvotes: 0

Related Questions