SQL Server - TIMETABLE - PIVOT -Getting NULL Values

Asked By Chiranjeevi S on 24-Apr-12 10:01 AM
Earn up to 30 extra points for answering this tough question.

hi

These are the tables

------------------TABLES---------------

CREATE TABLE PERIOD(PID INT IDENTITY, PNAME VARCHAR(50))

ALTER TABLE PERIOD ADD CONSTRAINT PK_PID PRIMARY KEY(PID)

CREATE TABLE SUBJECT(SID INT IDENTITY, SNAME VARCHAR(50))

ALTER TABLE SUBJECT ADD CONSTRAINT PK_SID PRIMARY KEY(SID)

CREATE TABLE TIMETABLE (TID INT IDENTITY, WEEKDAY INT, PID INT, SID INT)

ALTER TABLE TIMETABLE ADD CONSTRAINT PK_TID PRIMARY KEY(TID)

ALTER TABLE TIMETABLE ADD CONSTRAINT FK_PID FOREIGN KEY(PID) REFERENCES PERIOD(PID)

ALTER TABLE TIMETABLE ADD CONSTRAINT FK_SID FOREIGN KEY(PID) REFERENCES SUBJECT(SID)

------------------DATA-----------------

INSERT INTO PERIOD (PNAME) VALUES ('P1'), ('P2'), ('P3'), ('P4'), ('P5'), ('P6')

INSERT INTO SUBJECT (SNAME) VALUES('MATHS'), ('PHYSICS'), ('SCIENCE'), ('TELUGU'), ('ENGLISH'), ('HINDI')

INSERT INTO TIMETABLE (WEEKDAY, PID, SID) VALUES

 (1, 1, 1), (1, 2, 2), (1, 3, 3), (1, 4, 4), (1, 5, 5), (1, 6, 6)

,(2, 1, 2), (2, 2, 3), (2, 3, 4), (2, 4, 5), (2, 5, 6), (2, 6, 1)

------------------QUERY---------------

SELECT WEEKDAY, [P1],[P2],[P3],[P4],[P5],[P6]

FROM (

  SELECT  WEEKDAY, SNAME, PNAME

  FROM    TIMETABLE T

  INNER JOIN SUBJECT S ON S.SID = T.SID

  INNER JOIN PERIOD P ON P.PID = T.PID

) TAB PIVOT (MAX(SNAME) FOR PNAME IN([P1],[P2],[P3],[P4],[P5],[P6])) PVT

 

with the above query we are able to view the timetable like the following Thanks for the previous query, now i need some modifications in that if add one more column in select query 
then i am getting problem

SELECT WEEKDAY, [P1],[P2],[P3],[P4],[P5],[P6],[1],[2],[3],[4],[5],[6]
FROM (
  SELECT  WEEKDAY, SNAME, PNAME, T.PID
  FROM    TIMETABLE T 
  INNER JOIN SUBJECT S ON S.SID = T.SID
  INNER JOIN PERIOD P ON P.PID = T.PID
) TAB PIVOT (MAX(SNAME) FOR PNAME IN([P1],[P2],[P3],[P4],[P5],[P6])) PVT
PIVOT (MAX(PID) FOR PID IN([1],[2],[3],[4],[5],[6])) PVT

now with this query i am getting un necessary columns. i need only 12 columns(p6 * 2) and  4 rows

Pat Hartman replied to Chiranjeevi S on 24-Apr-12 04:47 PM
What are the [1] - [6] columns and why did you add them to the select but not the pivot?
Chiranjeevi S replied to Pat Hartman on 24-Apr-12 11:34 PM
hi

Those are the PID's I need them also while retrieving the query (even though we have period name).
Chintan Vaghela replied to Chiranjeevi S on 25-Apr-12 02:01 AM

Hello,

You need to create two individual query and stored this two query result into table variable. After then you need to take inner join on both table to get desired result.

Step : 1 create two table variable

 

DECLARE @TempTable1 TABLE (

WEEKDAY varchar(30),

P1 varchar(30),

P2  varchar(30),

P3  varchar(30),

P4  varchar(30),

P5  varchar(30),

P6  varchar(30))

 

 

DECLARE @TempTable2 TABLE (

WEEKDAY varchar(30),

[1] varchar(30),

[2]  varchar(30),

[3]  varchar(30),

[4]  varchar(30),

[5]  varchar(30),

[6]  varchar(30))

Step : 2 store pivot table result into two table  variable respectively

 

INSERT INTO @TempTable1(WEEKDAY, P1, P2,P3,P4,P5,P6)

SELECT *

FROM

(

SELECT  WEEKDAY, SNAME, PNAME

  FROM    TIMETABLE T

  INNER JOIN SUBJECT S ON S.SID = T.SID

  INNER JOIN PERIOD P ON P.PID = T.PID

 

) PIV

PIVOT

(

MAX(SNAME) FOR PName in ([P1],[P2],[P3],[P4],[P5],[P6])

) as c

 

INSERT INTO @TempTable2(WEEKDAY, [1], [2],[3],[4],[5],[6])

SELECT *

FROM

(

SELECT  WEEKDAY, SNAME,T.PID

  FROM    TIMETABLE T

  INNER JOIN SUBJECT S ON S.SID = T.SID

  INNER JOIN PERIOD P ON P.PID = T.PID

 

) PIV

PIVOT

(

MAX(SNAME) FOR PID in ([1],[2],[3],[4],[5],[6])

) AS chld

 

 

Step : 3 inner join with this two table based on weekday field and get desired result

 

 

Select Table1.WEEKDAY,Table1.P1,Table1.P2,Table1.P3,Table1.P4,Table1.P5,Table1.P6,

Table2.[1],Table2.[2],Table2.[3],Table2.[4],Table2.[5],Table2.[6]

  from @TempTable1 as Table1 INNER JOIN @TempTable2 as Table2

 on Table1.WEEKDAY = Table2.WEEKDAY

 

Note :: check it and let me know your feedback

 

Hope this helpful!

Thanks

 

 

Somesh Yadav replied to Chiranjeevi S on 25-Apr-12 04:22 AM

you were very close try this:

create table #Students
(
    StudentID int
identity primary key,
    Name nvarchar
(50)
)
create table #Times
(
    TimeID int
identity primary key
   
,Name nvarchar(10)
)
create table #Days
(
    DayID int
identity primary key
   
,Name nvarchar(20)
)
create table #TimeTable
(
    StudentID int
references #Students(StudentID)
   
,TimeID int references #Times(TimeID)
   
,DayID int references #Days(DayID)
   
,Value nvarchar(50)
)

insert #Times values('t1')
insert #Times values('t2')
insert #Times values('t3')

insert #Days values('sunday')
insert #Days values('monday')
insert #Days values('tuesday')
insert #Days values('wednesday')
insert #Days values('thursday')
insert #Days values('friday')
insert #Days values('saturday')
insert #Students values('ahmad')
insert #TimeTable values(1,1,1,'asp')

SELECT *
FROM
(
   
SELECT     d.DayID, d.Name, t.Name AS Expr1, tt.Value
   
FROM         #Times t
   
CROSS JOIN #Days d
   
LEFT OUTER JOIN #TimeTable tt
       
ON d.DayID = tt.DayID
       
AND t.TimeID = tt.TimeID) AS d_1
PIVOT
(max (Value) FOR [Expr1]
IN (t1, t2, t3)) AS P


drop table #Students
drop table #Times
drop table #Days
drop table #TimeTable

Chintan Vaghela replied to Chiranjeevi S on 25-Apr-12 06:43 AM

Hello,

Note :: Refere above post , If you want to display subjected instead of subject name then goes to following  query

 

If you want to display Subject ID then change Table table2 varaible Query as following way

 

Take S.SID instead of SName

 

INSERT INTO @TempTable2(WEEKDAY, [1], [2],[3],[4],[5],[6])

 

SELECT *

 

FROM

 

(

 

SELECT  WEEKDAY, S.SID,T.PID

 

  FROM    TIMETABLE T

 

  INNER JOIN SUBJECT S ON S.SID = T.SID

 

  INNER JOIN PERIOD P ON P.PID = T.PID

 

 

 

) PIV

 

PIVOT

 

(

 

MAX(SID) FOR PID in ([1],[2],[3],[4],[5],[6])

 

) AS chld

 

 

 

Hope this helpful!

Thanks

 

 

Chiranjeevi S replied to Chintan Vaghela on 25-Apr-12 06:57 AM
Hi

Thanks for replying me, but the [1], [2],...... are not the new columns they are the IDs of P1, P2, P3, ..... and [1],[2], .... all need to come from a table PERIOD (pid, pname).

Once again thanks for reply.
Chintan Vaghela replied to Chiranjeevi S on 26-Apr-12 01:10 AM
Hello,

then replace [1],[2] with your period table id field value.

did you get any problem for above query then let me know
aneesa replied to Chiranjeevi S on 01-May-12 02:59 AM
ADD DISTINCT CLAUSE SELECT WEEKDAY, [P1],[P2],[P3],[P4],[P5],[P6],[1],[2],[3],[4],[5],[6]
 
FROM (
 
  SELECT DISTINCT  WEEKDAY, SNAME, PNAME, T.PID
 
  FROM    TIMETABLE T
 
  INNER JOIN SUBJECT S ON S.SID = T.SID
 
  INNER JOIN PERIOD P ON P.PID = T.PID
 
) TAB PIVOT (MAX(SNAME) FOR PNAME IN([P1],[P2],[P3],[P4],[P5],[P6])) PVT
 
PIVOT (MAX(PID) FOR PID IN([1],[2],[3],[4],[5],[6])) PVT