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:

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.

• Lennart - Slava Ukraini: Create a db-fiddle or similar for your setup
• Ronaldo: The How to create a Minimal, Complete, and Verifiable Example for database-related questions should help you write your question in a way that will make it easier for us to help you.

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