Sql Custom Pivot - Is this possible?

Question

I am using sql server 2014

I have a set of data rows like this in #TEMP_TABLE:

SCORE_ID     DAY_ID      DAY     OPEN    CLOSED     DATE       STATUS
   1          1          Mon     1pm      4pm     1/1/17     green
   2          1          Mon     2pm      4pm     1/1/17     green
   3          1          Mon     3pm      5pm     1/1/17     red
   4          1          Mon     4pm      6pm     1/2/17     yellow
   5          1          Mon     5pm      7pm     1/2/17     yellow

   6          2          Tues    1pm      4pm     1/1/17     green
   7          2          Tues    2pm      4pm     1/1/17     green
   8          2          Tues    3pm      5pm     1/1/17     red
   9          2          Tues    4pm      6pm     1/2/17     yellow
   10         2          Tues    5pm      7pm     1/2/17     yellow

   11         5          Fri     12am     12am    1/1/17     green

I want to try and pivot my data table so it can look like this. The column names will always be constant, but the values will vary. We can ignore SCORE_ID and DAY_ID.:

 DATE       STATUS      Mon        Tues      Wed      Thurs       Fri   
 1/1/17     green       1-4pm      1-4pm      -         -         12am-12am
 1/1/17     green       2-4pm      2-4pm      -         -         -
 1/1/17     red         3-5pm      3-5pm      -         -         -
 1/2/17     yellow      4-6pm      4-6pm      -         -         -
 1/2/17     yellow      5-7pm      5-7pm      -         -         -

I tried to google how to use PIVOT, but I feel like the ones I did find don't really apply to how I want my data to be structured.

select DATE, STATUS, 'Mon', 'Tues', 'Wed, 'Thurs', Fri'
from (
    select DATE, STATUS
    from #TEMP_TABLE
) d

...

Is the way I want to structure it possible?

Answer 1

So generally, no, and here's why... you don't have a distinct way to identify the data set that you're looking for. You can massage the data a little at a time using common table expressions to get the number of hours like so:

with CTE as 
(select 
    DATE, 
    STATUS, 
    RTRIM([OPEN]) + '-' + CLOSED As 'Hours',
    CAST(DATE AS varchar) + STATUS As 'GroupID',
    DAY 
from StackQuestion),
CTE2 as
(select
    DATE,
    STATUS,
    CASE DAY WHEN 'Mon' THEN Hours ELSE '-' END as 'Mon',
    CASE DAY WHEN 'Tues' THEN Hours ELSE '-' END as 'Tues',
    CASE DAY WHEN 'Wed' THEN Hours ELSE '-' END as 'Wed',
    CASE DAY WHEN 'Thurs' THEN Hours ELSE '-' END as 'Thurs',
    CASE DAY WHEN 'Fri' THEN Hours ELSE '-' END as 'Fri',   
    HOURS
from CTE)
select * from CTE2

And that looks like this:

But that doesn't solve your problem. So what if we put a pivot table on the first CTE and used that as the basis for the pivot -- I'm going to use CTE here with the same expression it as above (to save space).

select * from CTE
PIVOT ( MAX(HOURS) for DAY in ([Mon],[Tues],[Wed],[Thurs],[Fri])) as pvt

And that result looks like this:

We can get a similar result out of CTE2 with the following:

select
    DATE, 
    STATUS, 
    MAX(Mon) as 'Mon', 
    MAX(Tues) as 'Tues',
    MAX(Wed) as 'Wed',
    MAX(Thurs) as 'Thurs',
    MAX(Fri) as 'Fri'
from CTE2 group by DATE, STATUS, GroupID

But the underlying issue will stay the same -- in that there's no way to identify a 'Status' for a 'Date' that has the same open/closed times. Even if you were to key it based on those times, the 12am-12pm would be in a different row, and so would subsequent days if they didn't happen to line up with the first days' data. What you need is some sort of ID that is unique to what you're looking at that will allow you to describe it in the query in such a way that it doesn't trip up the aggregate functions.

Hope this helps...