question

gautham.gn avatar image
gautham.gn asked

Using pivot in this case...

--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 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
pivot
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

The site runs on voting. Please indicate all helpful answers by clicking on the thumbs up symbols next to those answers. Please indicate any one answer that lead to the best solution by clicking on the check mark next to that answer.
0 Likes 0 ·
KenJ avatar image
KenJ answered
a PIVOT would look something like this: SELECT Name, [Maths], [Hindi], [English] FROM (SELECT name, subject, marks FROM marks_581) AS SourceTable PIVOT ( max(marks) FOR Subject IN ([Maths], [Hindi], [English]) ) AS PivotTable ORDER BY Name; although it doesn't need to be a pivot to get the same result. Take this self-join for example: select maths.name as Name, maths.Marks as Maths, hindi.Marks as Hindi, english.Marks as English from #marks_581 as maths left join #marks_581 as hindi on maths.name = hindi.Name and hindi.Subject = 'Hindi' left join marks_581 as english on english.name = maths.Name and english.Subject = 'English' where maths.Subject = 'Maths' order by Name; either way, you should see something like the following: Name Maths Hindi English ------------------------------ ----------- ----------- ----------- Dishant 45 49 40 Gautham 45 49 41 Pranay 45 50 41
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Pavel Pawlowski avatar image
Pavel Pawlowski answered
As @KenJ mentioned, you can use PIVOT and also a way without pivot. But instead of self JOIN it is better to go by CASE statement in aggregation fuction. select Name ,MAX(CASE WHEN [Subject] = 'Maths' THEN [Marks] ELSE NULL END) AS [Maths] ,MAX(CASE WHEN [Subject] = 'Hindi' THEN [Marks] ELSE NULL END) AS [Hindi] ,MAX(CASE WHEN [Subject] = 'English' THEN [Marks] ELSE NULL END) AS [English] from marks_581 GROUP BY Name Result is the same as in case of @KenJ example but will produce much less reads.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I knew I left something out: the legacy pivot! good catch :)
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.