如何使用pivot生成每周TimeTable

Question

我想使用sql server pivot生成每周时刻表。

我的数据库里有这些表格。

        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')

我希望有列(DayID、DayName、t1、t2、t3)

我正在使用此查询，但由于在向TimeTable表中插入记录时使用Max(值)聚合函数，此查询显示列t1、t2和t3有一个值。但是，t2和t3应为null

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

例如，执行这些cmds后，所有列的asp值都会显示出来。

        insert Students values('ahmad')
            insert TimeTable values(1,1,1,'asp') 

Answer 1

你错过了一个从TimeTable到Times的匹配：

 AND dbo.Times.TimeID = dbo.TimeTable.TimeID

Answer 2

你已经很接近了，试试这个：

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