sql - 我想转表

Question

我想按照下面给出的格式对数据进行透视，在下面，我放置了一个代码，用于使用我已有的数据创建表。

CREATE TABLE #temp
(
SHOW     VARCHAR(100),
moviename    VARCHAR(100),
ticketrate    INT,
collectiondate    DATETIME,
totalcaroccupancy  DECIMAL(18,2)
)

INSERT INTO #temp
(
SHOW,
moviename,
ticketrate,
collectiondate,
totalcaroccupancy
)
SELECT 'Evening','DASSEHRA',70,'2018-10-26 00:00:00.000','11440.00' union all
SELECT 'Evening','DASSEHRA',70,'2018-10-27 00:00:00.000','16016.00' union all
SELECT 'Night','Test Thugs',70,'2018-10-26 00:00:00.000','14560.00' union all
SELECT 'Night','Test Thugs',70,'2018-10-27 00:00:00.000','15600.00'

我当前的表数据如下:

SHOW       moviename     ticketrate    collectiondate      totalcaroccupancy
Evening    DASSEHRA      70            2018-10-26 00:00:00.000    11440.00
Evening    DASSEHRA      70            2018-10-27 00:00:00.000    16016.00
Night      Test Thugs    70            2018-10-26 00:00:00.000    14560.00
Night      Test Thugs    70            2018-10-27 00:00:00.000    15600.00

我想要如下输出:

collectiondate             eveningticketrate    eveningticketrate    eveningMovieName    nightMovieName    eveningtotalcaroccupancy    nighttotalcaroccupancy
2018-10-26 00:00:00.000    70                   70                   DASSEHRA            Test Thugs        11440.00                    14560.00
2018-10-27 00:00:00.000    70                   70                   DASSEHRA            Test Thugs        16016.00                    15600.00

Answer 1

只需使用如下所示的条件 case 语句

select  collectiondate,
    max(case when SHOW = 'Evening' then ticketrate end) as eveningticketrate,
    max(case when SHOW = 'Night' then ticketrate end) as nightticketrate,
    max(case when SHOW = 'Evening' then moviename end) as eveningmoviename,
    max(case when SHOW = 'Night' then moviename end) as nightmoviename,
    max(case when SHOW = 'Evening' then totalcaroccupancy end) as eveningtotalcaroccupancy,
    max(case when SHOW = 'Night' then totalcaroccupancy end) as nighttotalcaroccupancy
from    #temp
group by collectiondate