[Challenge] 電影時刻表

Beyond Relationa TSQL Beginners Challenge 8

這個問題有點奇怪，給了兩個 Table Data，但結果好像只要一個 Table Data 就可以產生。

• 資料來源

Table: MovieReleaseDates

Movie1     Movie2  Movie3  Movie4  Movie5
----------  ---------- ---------- ---------- ----------
2010-01-20  2010-02-15  2010-02-02  2010-03-31  2010-04-16 
2010-01-22  2010-02-16  2010-02-04  2010-04-05  2010-04-18 

Table: MovieReleasedIn

City   MovieNames ReleaseDate
--------- ---------- -----------
Bangalore Movie1  2010-01-22 
Bangalore Movie2  2010-02-15 
Bangalore Movie3  2010-02-04 
Bangalore Movie4  2010-04-05 
Bangalore Movie5  2010-04-16     

• 產生結果

City   Sun Mon     Tue Wed Thu    Fri     Sat  
--------- --- ------------ --- --- ------------- ------------- ---  
Bangalore NA  NA     NA  NA  NA    Movie1(01/22) NA 
Bangalore NA  Movie2(02/15)NA  NA  NA    NA         NA 
Bangalore NA  NA     NA  NA  Movie3(02/04) NA         NA 
Bangalore NA  Movie4(04/05)NA  NA  NA    NA         NA 
Bangalore NA  NA     NA  NA  NA    Movie5(04/16) NA  

• 個人解法

DECLARE @tblMovieReleasedIn TABLE
(
  City VARCHAR(20), 
  MovieNames VARCHAR(20),
  ReleaseDate Datetime
)
INSERT INTO @tblMovieReleasedIn
SELECT 'Bangalore','Movie1','01/22/2010' UNION ALL
SELECT 'Bangalore','Movie2','02/15/2010' UNION ALL
SELECT 'Bangalore','Movie3','02/04/2010' UNION ALL
SELECT 'Bangalore','Movie4','04/05/2010' UNION ALL
SELECT 'Bangalore','Movie5','04/16/2010' 

SELECT 
  City , 
  ISNULL([1],'NA') AS Sun , 
  ISNULL([2],'NA') AS Mon , 
  ISNULL([3],'NA') AS Tue , 
  ISNULL([4],'NA') AS Wed , 
  ISNULL([5],'NA') AS Thu , 
  ISNULL([6],'NA') AS Fri , 
  ISNULL([7],'NA') AS Sat
FROM
  (
    SELECT 
      City , 
      MovieNames , 
      MovieNames + '(' + LEFT(CONVERT(char(10),ReleaseDate,101),5) + ')' AS Data , 
      DATEPArt(dw,ReleaseDate) AS dw 
    FROM  @tblMovieReleasedIn
  ) AS P
PIVOT
  (
    MAX(Data) FOR dw IN ([1],[2],[3],[4],[5],[6],[7])
  ) AS PV
ORDER BY City

參考資料
• TSQL Beginners Challenge 8 - Identify the release dates of movies on different cities
• Solutions - 兩個 Table Data 都有使用到