Pivot Not Working
-
I have a database table with the following schema
CREATE TABLE [dbo].[TestScores](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Student] [nvarchar](25) NOT NULL,
[English] [nvarchar](25) NULL,
[Physics] [nvarchar](25) NULL,
[Mathematics] [nvarchar](25) NULL,
[Engineering] [nvarchar](25) NULL,
PRIMARY KEY (ID)
)// Sample Data
Insert Into TestScores (Student, English, Physics, Mathematics, Engineering)
Values ('Jane', A, B, A, A);Insert Into TestScores (Student, English, Physics, Mathematics, Engineering)
Values ('Michelle', A, A, B, A);Insert Into TestScores (Student, English, Physics, Mathematics, Engineering)
Values ('Dan, A, A, A, B);I would like to display the results like the following
Student Jane Michelle Dan
English A A A
Physics B A A
Mathematics A B A
Engineering A A B
I wanted to first Unpivot the data then Pivot the data but I just could not use the Pivot operator on the given data. Below is my query
Select * from TestScores
Unpivot(Course for Courses in (Student, English, Physics, Mathematics, Engineering))as upv
Pivot(course for Student in (Jane, Michelle, Dan)) as pv -
I have a database table with the following schema
CREATE TABLE [dbo].[TestScores](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Student] [nvarchar](25) NOT NULL,
[English] [nvarchar](25) NULL,
[Physics] [nvarchar](25) NULL,
[Mathematics] [nvarchar](25) NULL,
[Engineering] [nvarchar](25) NULL,
PRIMARY KEY (ID)
)// Sample Data
Insert Into TestScores (Student, English, Physics, Mathematics, Engineering)
Values ('Jane', A, B, A, A);Insert Into TestScores (Student, English, Physics, Mathematics, Engineering)
Values ('Michelle', A, A, B, A);Insert Into TestScores (Student, English, Physics, Mathematics, Engineering)
Values ('Dan, A, A, A, B);I would like to display the results like the following
Student Jane Michelle Dan
English A A A
Physics B A A
Mathematics A B A
Engineering A A B
I wanted to first Unpivot the data then Pivot the data but I just could not use the Pivot operator on the given data. Below is my query
Select * from TestScores
Unpivot(Course for Courses in (Student, English, Physics, Mathematics, Engineering))as upv
Pivot(course for Student in (Jane, Michelle, Dan)) as pvI have never used unpivot by this article may help with the multi column pivot Pivot two or more columns in SQL Server 2005[^]
Never underestimate the power of human stupidity RAH
-
I have a database table with the following schema
CREATE TABLE [dbo].[TestScores](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Student] [nvarchar](25) NOT NULL,
[English] [nvarchar](25) NULL,
[Physics] [nvarchar](25) NULL,
[Mathematics] [nvarchar](25) NULL,
[Engineering] [nvarchar](25) NULL,
PRIMARY KEY (ID)
)// Sample Data
Insert Into TestScores (Student, English, Physics, Mathematics, Engineering)
Values ('Jane', A, B, A, A);Insert Into TestScores (Student, English, Physics, Mathematics, Engineering)
Values ('Michelle', A, A, B, A);Insert Into TestScores (Student, English, Physics, Mathematics, Engineering)
Values ('Dan, A, A, A, B);I would like to display the results like the following
Student Jane Michelle Dan
English A A A
Physics B A A
Mathematics A B A
Engineering A A B
I wanted to first Unpivot the data then Pivot the data but I just could not use the Pivot operator on the given data. Below is my query
Select * from TestScores
Unpivot(Course for Courses in (Student, English, Physics, Mathematics, Engineering))as upv
Pivot(course for Student in (Jane, Michelle, Dan)) as pvYou have a problem with your Unpivot - try removing 'Student' from the column list i.e.
Unpivot(Course for Courses in (English, Physics, Mathematics, Engineering))as upv
There is also a problem with the Pivot - you need to use a summary function e.g.
Pivot(MAX(course) for Student in (Jane, Michelle, Dan)) as pv
Unfortunately if you fix those two problems you get the following results
ID Courses Jane Michelle Dan
1 Engineering A NULL NULL
2 Engineering NULL A NULL
3 Engineering NULL NULL B
1 English A NULL NULL
2 English NULL A NULL
3 English NULL NULL A
1 Mathematics A NULL NULL
2 Mathematics NULL B NULL
3 Mathematics NULL NULL A
1 Physics B NULL NULL
2 Physics NULL A NULL
3 Physics NULL NULL AThis is where the article mentioned by @Mycroft-Holmes comes into play OR you can fiddle it like this
select Courses, MAX(ISNULL(Jane,'')) as Jane, MAX(ISNULL(Michelle,'')) as Michelle, MAX(ISNULL(Dan,'')) as Dan
from
(Select * from TestScores
Unpivot(Course for Courses in (English, Physics, Mathematics, Engineering))
as upv ) UPV
Pivot(MAX(Course) for Student in (Jane, Michelle, Dan)) as pv
GROUP BY Courses -
You have a problem with your Unpivot - try removing 'Student' from the column list i.e.
Unpivot(Course for Courses in (English, Physics, Mathematics, Engineering))as upv
There is also a problem with the Pivot - you need to use a summary function e.g.
Pivot(MAX(course) for Student in (Jane, Michelle, Dan)) as pv
Unfortunately if you fix those two problems you get the following results
ID Courses Jane Michelle Dan
1 Engineering A NULL NULL
2 Engineering NULL A NULL
3 Engineering NULL NULL B
1 English A NULL NULL
2 English NULL A NULL
3 English NULL NULL A
1 Mathematics A NULL NULL
2 Mathematics NULL B NULL
3 Mathematics NULL NULL A
1 Physics B NULL NULL
2 Physics NULL A NULL
3 Physics NULL NULL AThis is where the article mentioned by @Mycroft-Holmes comes into play OR you can fiddle it like this
select Courses, MAX(ISNULL(Jane,'')) as Jane, MAX(ISNULL(Michelle,'')) as Michelle, MAX(ISNULL(Dan,'')) as Dan
from
(Select * from TestScores
Unpivot(Course for Courses in (English, Physics, Mathematics, Engineering))
as upv ) UPV
Pivot(MAX(Course) for Student in (Jane, Michelle, Dan)) as pv
GROUP BY CoursesThank you so much for replying. Your solution is 98 percent there, the only problem is that the courses in the result are listed out of order. In my scenario the order of the courses in the result is trivial. However, in other cases where I have to let's say show companies' earnings by quarter, show companies' stocks by quarter, etc., it will be important. How can I modify my solution to show the courses in their original order? Thanks again for your help.
-
Thank you so much for replying. Your solution is 98 percent there, the only problem is that the courses in the result are listed out of order. In my scenario the order of the courses in the result is trivial. However, in other cases where I have to let's say show companies' earnings by quarter, show companies' stocks by quarter, etc., it will be important. How can I modify my solution to show the courses in their original order? Thanks again for your help.
The only solution that comes to mind at the moment (I'll keep thinking!) is to have a separate table listing the courses with an Id field that reflects the order you require e.g.
DECLARE @courseNames table (id int identity(1,1), course nvarchar(25))
insert into @courseNames values ('English'),('Physics'),('Mathematics'),('Engineering')It could just as easily be a permanent table as a table variable. You could use such a table to generate dynamic SQL for the PIVOT if more courses are added). You can then join the previous query to this table and ORDER BY the id on the course table e.g.
select cn.id, Courses, MAX(ISNULL(Jane,'')) as Jane, MAX(ISNULL(Michelle,'')) as Michelle, MAX(ISNULL(Dan,'')) as Dan
from
(Select * from TestScores
Unpivot(Course for Courses in (English, Physics, Mathematics, Engineering))
as upv ) UPV
Pivot(MAX(Course) for Student in (Jane, Michelle, Dan)) as pv
inner join @courseNames cn ON pv.Courses=cn.course
GROUP BY cn.id, Courses
ORDER BY cn.idor if you don't want to include the ID number in the output you could use a CTE ...
;with q as
(
select Courses, MAX(ISNULL(Jane,'')) as Jane, MAX(ISNULL(Michelle,'')) as Michelle, MAX(ISNULL(Dan,'')) as Dan
from
(Select * from TestScores
Unpivot(Course for Courses in (English, Physics, Mathematics, Engineering))
as upv ) UPV
Pivot(MAX(Course) for Student in (Jane, Michelle, Dan)) as pv
GROUP BY Courses
)
select q.Courses, q.Jane, q.Michelle, q.Dan
FROM q
inner join @courseNames cn ON q.Courses=cn.course
ORDER BY cn.id -
The only solution that comes to mind at the moment (I'll keep thinking!) is to have a separate table listing the courses with an Id field that reflects the order you require e.g.
DECLARE @courseNames table (id int identity(1,1), course nvarchar(25))
insert into @courseNames values ('English'),('Physics'),('Mathematics'),('Engineering')It could just as easily be a permanent table as a table variable. You could use such a table to generate dynamic SQL for the PIVOT if more courses are added). You can then join the previous query to this table and ORDER BY the id on the course table e.g.
select cn.id, Courses, MAX(ISNULL(Jane,'')) as Jane, MAX(ISNULL(Michelle,'')) as Michelle, MAX(ISNULL(Dan,'')) as Dan
from
(Select * from TestScores
Unpivot(Course for Courses in (English, Physics, Mathematics, Engineering))
as upv ) UPV
Pivot(MAX(Course) for Student in (Jane, Michelle, Dan)) as pv
inner join @courseNames cn ON pv.Courses=cn.course
GROUP BY cn.id, Courses
ORDER BY cn.idor if you don't want to include the ID number in the output you could use a CTE ...
;with q as
(
select Courses, MAX(ISNULL(Jane,'')) as Jane, MAX(ISNULL(Michelle,'')) as Michelle, MAX(ISNULL(Dan,'')) as Dan
from
(Select * from TestScores
Unpivot(Course for Courses in (English, Physics, Mathematics, Engineering))
as upv ) UPV
Pivot(MAX(Course) for Student in (Jane, Michelle, Dan)) as pv
GROUP BY Courses
)
select q.Courses, q.Jane, q.Michelle, q.Dan
FROM q
inner join @courseNames cn ON q.Courses=cn.course
ORDER BY cn.idThank you for all your help, your solution using table variable works like a charm.
-
Thank you for all your help, your solution using table variable works like a charm.
-
Thank you for all your help, your solution using table variable works like a charm.
Psst! Enhance his rep by upvoting his response, green angle on the left!
Never underestimate the power of human stupidity RAH
