I have two queries that each perform an unpivot and the result comes out looking as I would intend for each, I would like to put these together into one select query.
Original Table:
| CCN | C_FORECAST_YEAR | CUSTOMER | ITEM | REVISION | MARKET | C_SALFOR_RVRT | COMM_PERCENT | CONTRACT_PRICE1 | CONTRACT_PRICE2 | CONTRACT_PRICE3 | CONTRACT_PRICE4 | CONTRACT_PRICE5 | CONTRACT_PRICE6 | CONTRACT_PRICE7 | CONTRACT_PRICE8 | CONTRACT_PRICE9 | CONTRACT_PRICE10 | CONTRACT_PRICE11 | CONTRACT_PRICE12 | QTY1 | QTY2 | QTY3 | QTY4 | QTY5 | QTY6 | QTY7 | QTY8 | QTY9 | QTY10 | QTY11 | QTY12 | ACT_PRICE1 | ACT_PRICE2 | ACT_PRICE3 | ACT_PRICE4 | ACT_PRICE5 | ACT_PRICE6 | ACT_PRICE7 | ACT_PRICE8 | ACT_PRICE9 | ACT_PRICE10 | ACT_PRICE11 | ACT_PRICE12 | ACT_QTY1 | ACT_QTY2 | ACT_QTY3 | ACT_QTY4 | ACT_QTY5 | ACT_QTY6 | ACT_QTY7 | ACT_QTY8 | ACT_QTY9 | ACT_QTY10 | ACT_QTY11 | ACT_QTY12 | LAST_ARCHIVE_DATE |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | 2023 1030 | 6A4R17 | ADCCNENGR | 2.000 | 2.390000 | 0.000000 | 0.000000 | 0.000000 | 0.000000 | 0.000000 | 0.000000 | 0.000000 | 0.000000 | 0.000000 | 0.000000 | 0.000000 | 0.000000 | 0.000000 | 19202.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | NULL |
| 1 | 2023 1030 | 6A4R19 | DCCENG | 80 | 2.000 | 0.000000 | 0.000000 | 0.000000 | 0.000000 | 0.000000 | 0.000000 | 0.000000 | 0.000000 | 0.000000 | 0.000000 | 0.000000 | 1.500000 | 0.000000 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | NULL |
Query 1:
Select UNPVT.C_FORECAST_YEAR, UNPVT.CUSTOMER, UNPVT.ITEM, UNPVT.C_SALFOR_RVRT,[CONTRACT_MONTH], CONTRACT_PRICE from
(SELECT C_FORECAST_YEAR, CUSTOMER,ITEM, C_SALFOR_RVRT,CONTRACT_PRICE1,CONTRACT_PRICE2,CONTRACT_PRICE3,CONTRACT_PRICE4,CONTRACT_PRICE5,CONTRACT_PRICE6,CONTRACT_PRICE7,CONTRACT_PRICE8,CONTRACT_PRICE9,CONTRACT_PRICE10,CONTRACT_PRICE11,CONTRACT_PRICE12 FROM C_SALFOR)P
UNPIVOT
(CONTRACT_PRICE FOR [CONTRACT_MONTH] in (CONTRACT_PRICE1,CONTRACT_PRICE2,CONTRACT_PRICE3,CONTRACT_PRICE4,CONTRACT_PRICE5,CONTRACT_PRICE6,CONTRACT_PRICE7,CONTRACT_PRICE8,CONTRACT_PRICE9,CONTRACT_PRICE10,CONTRACT_PRICE11,CONTRACT_PRICE12))AS UNPVT
WHERE UNPVT.C_FORECAST_YEAR = '2023'
Results:
| C_FORECAST_YEAR | CUSTOMER | ITEM | C_SALFOR_RVRT | CONTRACT_MONTH | CONTRACT_PRICE |
|---|---|---|---|---|---|
| 2023 | 1030 | 6A4R17 | CONTRACT_PRICE1 | 8.390000 | |
| 2023 | 1030 | 6A4R17 | CONTRACT_PRICE2 | 0.000000 |
Query 2:
Select C_FORECAST_YEAR, CUSTOMER, ITEM, C_SALFOR_RVRT,[QTY_MONTH], QTY from
(SELECT C_FORECAST_YEAR, CUSTOMER,ITEM, C_SALFOR_RVRT,QTY1,QTY2,QTY3,QTY4,QTY5,QTY6,QTY7,QTY8,QTY9,QTY10,QTY11,QTY12 FROM C_SALFOR)Q
UNPIVOT
(QTY FOR [QTY_MONTH] in (QTY1,QTY2,QTY3,QTY4,QTY5,QTY6,QTY7,QTY8,QTY9,QTY10,QTY11,QTY12))AS UNPVT2
WHERE C_FORECAST_YEAR = '2023'
Results:
| C_FORECAST_YEAR | CUSTOMER | ITEM | C_SALFOR_RVRT | QTY_MONTH | QTY |
|---|---|---|---|---|---|
| 2023 | 1030 | 6A4R17 | QTY1 | 19008 | |
| 2023 | 1030 | 6A4R17 | QTY2 | 00000 |
Desired result:
| C_FORECAST_YEAR | CUSTOMER | ITEM | C_SALFOR_RVRT | CONTRACT_MONTH | CONTRACT_PRICE | QTY_MONTH | QTY |
|---|---|---|---|---|---|---|---|
| 2023 | 1030 | 6A4R17 | CONTRACT_PRICE1 | 8.390000 | QTY1 | 19008 | |
| 2023 | 1030 | 6A4R17 | CONTRACT_PRICE2 | .000000 | QTY2 | 00000 |
You'll want to do the unpivot "manually":
with data as (
select C_FORECAST_YEAR, CUSTOMER, ITEM, C_SALFOR_RVRT,
case n when 1 then CONTRACT_PRICE1
when 2 then CONTRACT_PRICE2
when 3 then CONTRACT_PRICE3
when 4 then CONTRACT_PRICE4
when 5 then CONTRACT_PRICE5
when 6 then CONTRACT_PRICE6
when 7 then CONTRACT_PRICE7
when 8 then CONTRACT_PRICE8
when 9 then CONTRACT_PRICE9
when 10 then CONTRACT_PRICE10
when 11 then CONTRACT_PRICE11
when 12 then CONTRACT_PRICE12
end as CONTRACT_MONTH,
case n when 1 then QTY1
when 2 then QTY2
when 3 then QTY3
when 4 then QTY4
when 5 then QTY5
when 6 then QTY6
when 7 then QTY7
when 8 then QTY8
when 9 then QTY9
when 10 then QTY10
when 11 then QTY11
when 12 then QTY12
end as QTY_MONTH
from C_SALFOR cross apply
(values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12)) as u(n)
where C_FORECAST_YEAR = '2023' -- are you sure this is a character string?
)
select * from data
-- is there potential for missing values/incomplete years?
where QTY_MONTH is not null or CONTRACT_MONTH is not null;
You could also capture the values within the cross apply as:
(values (CONTRACT_PRICE1, QTY1),
(CONTRACT_PRICE2, QTY2),
-- ...
(CONTRACT_PRICE12, QTY12)
) as u(CONTRACT_MONTH, QTY_MONTH)
This would only work on SQL Server. The first version is a bit more portable/classic SQL. There are other ways to generate the list of numbers from 1 to 12.