Reformat Department Table

Write an SQL query to reformat the table such that there is a department id column and a revenue column for each month.

Return the result table in any order.

table name: department

Solution:

SELECT id,[Jan] as [Jan_Revenue],
		[Feb] as [Feb_Revenue],
		[Mar] as [Mar_Revenue],
		[Apr] as [Apr_Revenue],
		[May] as [May_Revenue],
		[Jun] as [Jun_Revenue],
		[Jul] as [Jul_Revenue],
		[Aug] as [Aug_Revenue],
		[Sep] as [Sep_Revenue],
		[Oct] as [Oct_Revenue],
		[Nov] as [Nov_Revenue],
		[Dec] as [Dec_Revenue]
		FROM   
(
    SELECT 
        id,revenue,[month]
       
    FROM 
        Department3
) t 
PIVOT(
    sum(revenue) 
    FOR month IN (
        [Jan],
        [Feb],
		[Mar],
		[Apr],
		[May],
		[Jun],
		[Jul],
		[Aug],
		[Sep],
		[Oct],
		[Nov],
		[Dec])
      
) AS pivot_table;

Output:

SQL Script:

Create table Department (id int, revenue int, month varchar(5))
insert into Department (id, revenue, month) values ('1', '8000', 'Jan')
insert into Department (id, revenue, month) values ('2', '9000', 'Jan')
insert into Department (id, revenue, month) values ('3', '10000', 'Feb')
insert into Department (id, revenue, month) values ('1', '7000', 'Feb')
insert into Department (id, revenue, month) values ('1', '6000', 'Mar')