6

I have a question I can't solve.

My query would be:

SELECT
    f.Title, p.StartOfShowing
FROM  
    PlayDates p 
INNER JOIN 
    PlayDateSections ps ON p.PlayDateId = ps.PlayDateId
INNER JOIN 
    Films f ON p.FilmId = f.FilmId
WHERE  
    DATEDIFF(d, p.StartOfShowing, '2018-04-30') = 0

The result of the table would be this:

A Quiet Place                      2018-04-30 12:40
A Quiet Place                      2018-04-30 15:05
A Quiet Place                      2018-04-30 17:30
A Quiet Place                      2018-04-30 19:55
A Quiet Place                      2018-04-30 22:20
Avengers : Infinity War            2018-04-30 11:10
Avengers : Infinity War            2018-04-30 11:30
Avengers : Infinity War            2018-04-30 12:00
Avengers : Infinity War            2018-04-30 13:00
Avengers : Infinity War            2018-04-30 13:20
Avengers : Infinity War            2018-04-30 14:00
Avengers : Infinity War            2018-04-30 14:20
Avengers : Infinity War            2018-04-30 14:40
Avengers : Infinity War            2018-04-30 15:10
Avengers : Infinity War            2018-04-30 16:10
Avengers : Infinity War            2018-04-30 16:30
Avengers : Infinity War            2018-04-30 17:10
Avengers : Infinity War            2018-04-30 17:30
Avengers : Infinity War            2018-04-30 17:50
Avengers : Infinity War            2018-04-30 18:20
Avengers : Infinity War            2018-04-30 19:20
Avengers : Infinity War            2018-04-30 19:40
Avengers : Infinity War            2018-04-30 20:20
Avengers : Infinity War            2018-04-30 20:40
Avengers : Infinity War            2018-04-30 21:00
Avengers : Infinity War            2018-04-30 21:30
Avengers : Infinity War            2018-04-30 22:50
Avengers : Infinity War  ATMOS     2018-04-30 12:40
Avengers : Infinity War  ATMOS     2018-04-30 15:50
Avengers : Infinity War  ATMOS     2018-04-30 19:00
Avengers : Infinity War  ATMOS     2018-04-30 22:10
Avengers : Infinity War  PLATINUM  2018-04-30 14:40
Avengers : Infinity War  PLATINUM  2018-04-30 17:50
Avengers : Infinity War  PLATINUM  2018-04-30 21:00
Avengers : Infinity War  PLATINUM  2018-04-30 23:30
Love Simon                         2018-04-30 14:25
Love Simon                         2018-04-30 19:15
Rampage                            2018-04-30 16:55
Rampage                            2018-04-30 21:45

However I wanted to have a pivot query with result like the ones below :

A Quiet Place                     4/30/2018  12:40  15:05  17:30  19:55  22:20
Avengers : Infinity War           4/30/2018  11:10  11:30  12:00  13:00  13:20
Avengers : Infinity War           4/30/2018  14:00  14:20  14:40  15:10  16:10
Avengers : Infinity War           4/30/2018  16:30  17:10  17:30  17:50  18:20
Avengers : Infinity War           4/30/2018  19:20  19:40  20:20  20:40  21:00
Avengers : Infinity War ATMOS     4/30/2018  12:40  15:50  19:00  22:10  NULL
Avengers : Infinity War PLATINUM  4/30/2018  23:30  14:40  17:50  21:00  NULL
Love Simon                        4/30/2018  19:15  14:25  NULL   NULL   NULL
Rampage                           4/30/2018  16:55  21:45  NULL   NULL   NULL
14

To your data set, I added a row number (ROW_NUMBER), and this field is divided by 5 (you wanted 5 columns in the output) and hold the rest and the hole number of this division in fields inRows , inColumns. Base on this, will do the pivot.

There is a special case when you divide by 5 and have rest = 0 ; for this I use case...

,RN / 5 + CASE WHEN RN % 5 = 0 THEN -1 ELSE 0 END AS inRows

,CASE WHEN RN % 5 = 0 THEN 5 ELSE RN % 5 END AS inCols

;WITH myCTE AS
(
SELECT
  Title,StartOfShowing
  ,ROW_NUMBER() OVER(PARTITION BY Title ORDER BY StartOfShowing ASC) AS RN
FROM
  tblResults
)
, myPivotSource AS 
(
SELECT Title,StartOfShowing
   --,RN
   --,(RN - 1 )/ 5  AS inRows
   ,RN / 5 + CASE WHEN RN % 5  = 0 THEN -1   ELSE 0  END AS inRows
   ,CASE WHEN RN % 5  = 0 THEN 5 ELSE RN % 5 END AS inCols
FROM
   myCTE
)

SELECT Title
    , CONVERT(varchar(10),[1],101) As dateofShowing 
    , CONVERT(varchar(5),[1],108) AS [1]
    , CONVERT(varchar(5),[2],108) AS [2]
    , CONVERT(varchar(5),[3],108) AS [3]
    , CONVERT(varchar(5),[4],108) AS [4]
    , CONVERT(varchar(5),[5],108) AS [5]
FROM 
   myPivotSource AS PS
   PIVOT 
   (
      MIN(StartOfShowing) FOR inCols IN ([1], [2], [3], [4], [5])
   )PV
ORDER BY Title, inRows

output of it:

Title                               dateofShowing   1       2       3       4       5
A Quiet Place                       04/30/2018      12:40   15:05   17:30   19:55   22:20
Avengers : Infinity War             04/30/2018      11:10   11:30   12:00   13:00   13:20
Avengers : Infinity War             04/30/2018      14:00   14:20   14:40   15:10   16:10
Avengers : Infinity War             04/30/2018      16:30   17:10   17:30   17:50   18:20
Avengers : Infinity War             04/30/2018      19:20   19:40   20:20   20:40   21:00
Avengers : Infinity War             04/30/2018      21:30   22:50   null    null    null
Avengers : Infinity War  ATMOS      04/30/2018      12:40   15:50   19:00   22:10   null
Avengers : Infinity War  PLATINUM   04/30/2018      14:40   17:50   21:00   23:30   null
Love Simon                          04/30/2018      14:25   19:15   null    null    null
Rampage                             04/30/2018      16:55   21:45   null    null    null

dbfiddle

  • Could the special case for "RN / 5 + CASE..." be rewritten simply as "(RN - 1)/5 as inRows" ? – IanF1 Apr 30 '18 at 14:55
  • @IanF1, yes, could be rewritten . For this query is the same output - the new interval will be : [0..4] / 5 , [5..9] / 5 , [10..14] / 5 ... I thought it was easy to understand that way – Sabin Bio Apr 30 '18 at 16:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.