SQL-Server Unpivot/Pivot Example

Question

i'm making an application to manage hotel bookings and i need to show the ocupation rate per month in a year. I made a query that kinda solves the problem but i want presented in another format.

My current query return the following table (2x12):

January|February|March|April| ..... and so on
   20      15     18     20   ..... and so on

And i want something like this (12x2):

January|20
February|15
March|18
... |...

This is my query:

Select  
        SUM(CASE WHEN datename(month, [CheckIn]) = 'January' or datename(month, [CheckOut]) = 'January' THEN 1 ELSE 0 END) January,
        SUM(CASE WHEN datename(month, [CheckIn]) = 'February' or datename(month, [CheckOut]) = 'February' THEN 1 ELSE 0 END) February,
        SUM(CASE WHEN datename(month, [CheckIn]) = 'March' or datename(month, [CheckOut]) = 'March' THEN 1 ELSE 0 END) March,
        SUM(CASE WHEN datename(month, [CheckIn]) = 'April' or datename(month, [CheckOut]) = 'April' THEN 1 ELSE 0 END)  April,
        SUM(CASE WHEN datename(month, [CheckIn]) = 'May' or datename(month, [CheckOut]) = 'May' THEN 1 ELSE 0 END)  May,
        SUM(CASE WHEN datename(month, [CheckIn]) = 'June' or datename(month, [CheckOut]) = 'June' THEN 1 ELSE 0 END) June,
        SUM(CASE WHEN datename(month, [CheckIn]) = 'July' or datename(month, [CheckOut]) = 'July' THEN 1 ELSE 0 END) July,
        SUM(CASE WHEN datename(month, [CheckIn]) = 'August' or datename(month, [CheckOut]) = 'August' THEN 1 ELSE 0 END) August,
        SUM(CASE WHEN datename(month, [CheckIn]) = 'September' or datename(month, [CheckOut]) = 'September' THEN 1 ELSE 0 END) September,
        SUM(CASE WHEN datename(month, [CheckIn]) = 'October' or datename(month, [CheckOut]) = 'October' THEN 1 ELSE 0 END) October,
        SUM(CASE WHEN datename(month, [CheckIn]) = 'November' or datename(month, [CheckOut]) = 'November' THEN 1 ELSE 0 END) November,
        SUM(CASE WHEN datename(month, [CheckIn]) = 'December' or datename(month, [CheckOut]) = 'December' THEN 1 ELSE 0 END) December
FROM {Booking} INNER JOIN {Status} ON {Booking}.[StatusId] = {Status}.[Id]
WHERE {Booking}.[CheckIn] >= @BeginDate AND {Booking}.[CheckOut]  <= @EndDate AND {Status}.[Label] <> 'Canceled'

Any help would be appreciated, i'm stuck and theres is not that much info on the web, thanks!

score 4 · Accepted Answer · answered Mar 13 '15 at 15:58

Looks like you're trying to do an UNPIVOT:

SELECT Month, CheckIns
FROM
  (Select  
        SUM(CASE WHEN datename(month, [CheckIn]) = 'January' or datename(month, [CheckOut]) = 'January' THEN 1 ELSE 0 END) January,
        SUM(CASE WHEN datename(month, [CheckIn]) = 'February' or datename(month, [CheckOut]) = 'February' THEN 1 ELSE 0 END) February,
        SUM(CASE WHEN datename(month, [CheckIn]) = 'March' or datename(month, [CheckOut]) = 'March' THEN 1 ELSE 0 END) March,
        SUM(CASE WHEN datename(month, [CheckIn]) = 'April' or datename(month, [CheckOut]) = 'April' THEN 1 ELSE 0 END)  April,
        SUM(CASE WHEN datename(month, [CheckIn]) = 'May' or datename(month, [CheckOut]) = 'May' THEN 1 ELSE 0 END)  May,
        SUM(CASE WHEN datename(month, [CheckIn]) = 'June' or datename(month, [CheckOut]) = 'June' THEN 1 ELSE 0 END) June,
        SUM(CASE WHEN datename(month, [CheckIn]) = 'July' or datename(month, [CheckOut]) = 'July' THEN 1 ELSE 0 END) July,
        SUM(CASE WHEN datename(month, [CheckIn]) = 'August' or datename(month, [CheckOut]) = 'August' THEN 1 ELSE 0 END) August,
        SUM(CASE WHEN datename(month, [CheckIn]) = 'September' or datename(month, [CheckOut]) = 'September' THEN 1 ELSE 0 END) September,
        SUM(CASE WHEN datename(month, [CheckIn]) = 'October' or datename(month, [CheckOut]) = 'October' THEN 1 ELSE 0 END) October,
        SUM(CASE WHEN datename(month, [CheckIn]) = 'November' or datename(month, [CheckOut]) = 'November' THEN 1 ELSE 0 END) November,
        SUM(CASE WHEN datename(month, [CheckIn]) = 'December' or datename(month, [CheckOut]) = 'December' THEN 1 ELSE 0 END) December
  FROM {Booking} INNER JOIN {Status} ON {Booking}.[StatusId] = {Status}.[Id]
  WHERE {Booking}.[CheckIn] >= @BeginDate AND {Booking}.[CheckOut]  <= @EndDate AND {Status}.[Label] <> 'Canceled'
) monthTotals
UNPIVOT
(CheckIns FOR Month IN
   (January, February, March, April, May, June, July, August, September, October, November, December)
) AS upvt

Here's a simplified SQLFiddle of this

And here's a doc page on PIVOT and UNPIVOT

Thanks, that was exactly what i was loooking for! – Miguel Morujão Mar 13 '15 at 16:02 — Miguel Morujão, Mar 13 '15 at 16:02

score 1 · Answer 2 · answered Mar 13 '15 at 17:04

You can make your case statements a little shorter/simpler so it's easier to read. MONTH() is a easier to read function and getting rid of else will mean if it simply return a null which SUM() treats like a 0

SELECT [Month], CheckIns
FROM
  (
  Select  
        SUM(CASE WHEN MONTH([CheckIn]) = 01 or MONTH([CheckOut]) = 01 THEN 1 END) January,
        SUM(CASE WHEN MONTH([CheckIn]) = 02 or MONTH([CheckOut]) = 02 THEN 1 END) February,
        SUM(CASE WHEN MONTH([CheckIn]) = 03 or MONTH([CheckOut]) = 03 THEN 1 END) March,
        SUM(CASE WHEN MONTH([CheckIn]) = 04 or MONTH([CheckOut]) = 04 THEN 1 END) April,
        SUM(CASE WHEN MONTH([CheckIn]) = 05 or MONTH([CheckOut]) = 05 THEN 1 END) May,
        SUM(CASE WHEN MONTH([CheckIn]) = 06 or MONTH([CheckOut]) = 06 THEN 1 END) June,
        SUM(CASE WHEN MONTH([CheckIn]) = 07 or MONTH([CheckOut]) = 07 THEN 1 END) July,
        SUM(CASE WHEN MONTH([CheckIn]) = 08 or MONTH([CheckOut]) = 08 THEN 1 END) August,
        SUM(CASE WHEN MONTH([CheckIn]) = 09 or MONTH([CheckOut]) = 09 THEN 1 END) September,
        SUM(CASE WHEN MONTH([CheckIn]) = 10 or MONTH([CheckOut]) = 10 THEN 1 END) October,
        SUM(CASE WHEN MONTH([CheckIn]) = 11 or MONTH([CheckOut]) = 11 THEN 1 END) November,
        SUM(CASE WHEN MONTH([CheckIn]) = 12 or MONTH([CheckOut]) = 12 THEN 1 END) December
  FROM [Booking] 

  INNER JOIN {Status} ON {Booking}.[StatusId] = {Status}.[Id]
  WHERE {Booking}.[CheckIn] >= @BeginDate AND {Booking}.[CheckOut]  <= @EndDate AND {Status}.[Label] <> 'Canceled'
) MONTHTotals
UNPIVOT
(
CheckIns FOR [Month] IN
   (January, February, March, April, May, June, July, August, September, October, November, December)
) AS upvt

SQL-Server Unpivot/Pivot Example

2 Answers2