Nsryjdtyk

i want TO PIVOT DATA AS per given below format,
in below i put a code for create table with data which i have already.

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'

my current table data as below :

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

i want output as below :

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

simply use conditional case statement as shown below

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

An alternative is to create 2 subqueries, one for evening, the other for night and join them together

SELECT evening.collectiondate, eveningticketrate, eveningmoviename, eveningtotalcaroccupancy,

    nightticketrate, nightmoviename, nighttotalcaroccupancy

FROM (

    SELECT collectiondate, ticketrate as eveningticketrate, moviename as eveningmoviename, totalcaroccupancy as eveningtotalcaroccupancy

    FROM #temp

    WHERE show = 'Evening') evening

INNER JOIN (

    SELECT collectiondate, ticketrate as nightticketrate, moviename as nightmoviename, totalcaroccupancy as nighttotalcaroccupancy

    FROM #temp

    WHERE show = 'Night') night ON evening.collectiondate = night.collectiondate

ORDER BY evening.collectiondate;