Need Help with SQL Query

Question

Hi Everyone,

I need your help with below output, need to covert columns data into rows, could you please help me.

CREATE TABLE Items(id int,

Branch int,

Name nvarchar(3),

Code nvarchar(3),

Nov2019 int,

Dec2019 int,

Jan2020 int,

Feb2020 int,

Mar2020 int)

Input

insert into Items values

(1 ,30, 'TU', 'N' ,1 ,7 ,2, 7 ,9),

(2 ,40 ,'OK', 'Y', 0, 3 ,4, 2, 6)

Output

Answer 1

Hi @SM

Try this query.

;with CTE as(
  select * from Items 
   unpivot(Quantity for date in (Nov2019,Dec2019,Jan2020,Feb2020,Mar2020)) t)
select id,Branch,Name,Code,right(date,4) as Year,
	   case left(date,3) when 'Nov' then 11
	        when 'Dec' then 12
			when 'Jan' then 1
			when 'Feb' then 2
			when 'Mar' then 3 end as Month,Quantity
from CTE;

Output:

Best regards,

Percy Tang

---

If the answer is the right solution, please click "Accept Answer". If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.