2

I've read many posts about using pivot to get the data in the format I want, but the more I read the more confused I get.

I have this data:

enter image description here

That I'm trying to get into a format similar to this: enter image description here

For the most part, everything I try results in an SQL error, and the only successful attempt I've had didn't return the data in the format I'm looking for.

Any help would be appreciated.

4

Something like:

select hour_of_day, 
       avg( case when day_of_week = 2 then item_count else null end ) Mondays,
       avg( case when day_of_week = 3 then item_count else null end ) Tuesdays,
       avg( case when day_of_week = 4 then item_count else null end ) Wednesdays,
       avg( case when day_of_week = 5 then item_count else null end ) Thursdays,
       avg( case when day_of_week = 6 then item_count else null end ) Fridays,
       avg( case when day_of_week = 7 then item_count else null end ) Saturdays,
       avg( case when day_of_week = 1 then item_count else null end ) Sundays
where ...
group by hour_of_day
| improve this answer | |
3

welcome to dba.stackexchange. It might help to paste your (unsuccesful) queries and the errors you got.

I am sorry other tipps and tutorials did not help you with your challenge. Since you are specifically asking for pivot, I suggest another documentation that appears (to me) to be quite straight forward:
https://docs.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-ver15

The basic pivot form looks like this:

SELECT <non-pivoted column>,  
  [first pivoted column] AS <column name>,  
  [second pivoted column] AS <column name>,  
  ...  
  [last pivoted column] AS <column name>  
FROM  
  (<SELECT query that produces the data>)   
  AS <alias for the source query>  
PIVOT  
(  
   <aggregation function>(<column being aggregated>)  
FOR   
   [<column that contains the values that will become column headers>]   
   IN ( [first pivoted column], [second pivoted column],  
   ... [last pivoted column])  
   ) AS <alias for the pivot table>  
<optional ORDER BY clause>;  

In your case that might turn out to something along those lines (I did not test this and have not finished it all):

SELECT TimesOfDay,  
  [1] AS Monday,  
  [2] AS Tuesday,  
  ...  
  [last pivoted column] AS <column name>  
FROM  
  (<SELECT query that produces the data>)   
  AS <alias for the source query>  
PIVOT  
(  
   sum(item_count)  
FOR   
   [day_of_week]   
   IN ( [1], [2],  
   ... [7])  
   ) AS <alias for the pivot table>  
<optional ORDER BY clause>;  

Hope that helps Andreas

| improve this answer | |

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.