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
0

1 Answer 1

15

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

2
  • Could the special case for "RN / 5 + CASE..." be rewritten simply as "(RN - 1)/5 as inRows" ?
    – IanF1
    Apr 30, 2018 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 B
    Apr 30, 2018 at 16:41

This site is temporarily in read-only mode and not accepting new answers.

Not the answer you're looking for? Browse other questions tagged .