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