use demo1
go

if object_id('sales') is not null
drop table sales
go

create table q1sales
(
productname nvarchar(20),
year int,
[1] int,
[2] int,
[3] int
)
go

set nocount on
insert into q1sales values('Accord', 1999, 983, 482, null)
insert into q1sales values('Accord', 2000, null, null, 396)
insert into q1sales values('Civic', 1999, 3984, null, 1098)
insert into q1sales values('Civic', 2000, null, 20, null)
set nocount off
go

create table sales
(
productname nvarchar(20),
year int,
month int,
quantity int
)
go

set nocount on
insert into sales values('Accord', 1999, 1, 983)
insert into sales values('Accord', 1999, 2, 482)
insert into sales values('Accord', 1999, 6, 389)
insert into sales values('Accord', 2000, 3, 396)
insert into sales values('Accord', 2000, 5, 4000)
insert into sales values('Civic', 1999, 1, 3984)
insert into sales values('Civic', 1999, 4, 298)
insert into sales values('Civic', 1999, 3, 1098)
insert into sales values('Civic', 1999, 5, 918)
insert into sales values('Civic', 2000, 2, 20)
insert into sales values('Civic', 2000, 4, 303)
set nocount off
go

if object_id('q1sales2') is not null
drop table q1sales2
go

select productname as P, year as Y, [1], [2], [3] into q1sales2 from q1sales
go

SELECT * FROM ( SELECT * FROM q1sales UNPIVOT ( quantity FOR month IN ([1], [2]) ) q1 
JOIN q1sales2 UNPIVOT ( quantity2 FOR month2 IN ([3], [2]) ) q2 
ON q1.ProductName = q2.P AND q1.Month = q2.Month2 AND q1.Month > 1 ) AS qsls 
PIVOT ( MIN(quantity) FOR month IN ([2]) ) s
go

if object_id('q1sales2') is not null
drop table q1sales2
go

if object_id('sales') is not null
drop table sales
go

if object_id('q1sales') is not null
drop table q1sales
go
