How to Pivot Multiple Columns in SQL Server

Question

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:

That I'm trying to get into a format similar to this:

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.

Answer 1

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

Answer 2

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://learn.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