Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
gautham.gn
Starting Member
19 Posts |
 Posted - 2013-11-22 : 02:24:25
|
| --I have a table like this., and i want the result set having Name,Subject1,Marks1,Subject2,Marks2,Subject3,marks3 as columns--and it should get 3 rows with different names and subject1 it should show english and marks correspong in marks1 and similarly subject2 and for subject3.. I am confused using pivot for this case.. Can u help me with dis??--Thanks.,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 allselect 'Dishant','Maths',45 union allselect 'Dishant','Hindi',49 union allselect 'Pranay','English',41 union allselect 'Pranay','Maths',45 union allselect 'Pranay','Hindi',50 union allselect 'Gautham','English',41 union allselect 'Gautham','Maths',45 union allselect 'Gautham','Hindi',49 select * from marks_581 |
|
|
vignesht50
Yak Posting Veteran
82 Posts |
Posted - 2013-11-22 : 03:08:04
|
| Like this..SELECT Name, SUM(CASE WHEN Subject= 'English' THEN Marks ELSE 0 END) AS English, SUM(CASE WHEN Subject= 'Maths' THEN Marks ELSE 0 END) AS Maths, SUM(CASE WHEN Subject= 'Hindi' THEN Marks ELSE 0 END) AS Hindi, SUM(Marks) AS TotalMarks FROM marks_581 GROUP BY Name |
 |
|
|
gautham.gn
Starting Member
19 Posts |
Posted - 2013-11-22 : 03:34:59
|
| I want the requirement as the table should contain the columns and data like this.. Name |Subject1|Marks1|Subject2|marks2|Subject3|marks3Dishant|English |40 |Maths |45 |hindi |49Gautham|English |40 |Maths |45 |hindi |49Pranay |English |40 |Maths |45 |hindi |49 |
|
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-11-22 : 04:25:12
|
| SELECT Name, MAX( CASE WHEN Subject= 'English' THEN [Subject] ELSE NULL END ) Subject1,SUM(CASE WHEN Subject= 'English' THEN Marks ELSE 0 END) AS English,MAX( CASE WHEN Subject= 'Maths' THEN [Subject] ELSE NULL END ) Subject2,SUM(CASE WHEN Subject= 'Maths' THEN Marks ELSE 0 END) AS Maths, MAX( CASE WHEN Subject= 'Hindi' THEN [Subject] ELSE NULL END ) Subject3,SUM(CASE WHEN Subject= 'Hindi' THEN Marks ELSE 0 END) AS Hindi,SUM(Marks) AS TotalMarks FROM marks_581GROUP BY Name--Chandu |
|
|
|
vignesht50
Yak Posting Veteran
82 Posts |
Posted - 2013-11-22 : 04:35:37
|
| 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_581GROUP BY NameORDER BY Name |
|
|
|
gautham.gn
Starting Member
19 Posts |
Posted - 2013-11-22 : 04:45:31
|
| Yeah got.. thanks man..:) |
|
|
|
|
|
|
|
|
Pivot