need help in sql temp table

Hi

What I am trying to do is create two separate temp table to achieve pivotting from two separate CTEs. I am unable to do it. Please can someone guide me on how to move forward

My Code

;with AML
as
(
select
case when len(PRODUCT_CODE) < 6 then right('000000'+product_code,6) else PRODUCT_CODE end as [Product Code],
sum(f.SALE_TOT_QTY) as UnitsSold,
'AMLStoresYTD' as SalesBD
from dbo.AGG_MLY_STR_PROD f with(nolock)
where f.STORE_CODE =0000 and f.DATE_KEY > (select min(date_key) from dim_date where year = (select d.YEAR from dim_date d where d.DATE_FLD = cast(getdate()-1 as date))) and f.SALE_TOT_QTY <> 0
group by case when len(PRODUCT_CODE) < 6 then right('000000'+product_code,6) else PRODUCT_CODE end

union all

select
    case when len(PRODUCT_CODE) < 6 then right('000000'+product_code,6)  else PRODUCT_CODE end as [Product Code],
    sum(f.SALE_TOT_QTY) as UnitsSold,
    'AMLStoresWTD' as SalesBD 
from dbo.AGG_WLY_STR_PROD f with(nolock)
where f.STORE_CODE =1111 and f.DATE_KEY = (select d.WEEK_END_DATE_KEY from dim_date d where d.DATE_FLD = cast(getdate()-1 as date)) and f.SALE_TOT_QTY <> 0
group by case when len(PRODUCT_CODE) < 6 then right('000000'+product_code,6)  else PRODUCT_CODE end

union all

select
    case when len(PRODUCT_CODE) < 6 then right('000000'+product_code,6)  else PRODUCT_CODE end as [Product Code],
    sum(f.SALE_TOT_QTY) as UnitsSold,
    'AMLLW' as SalesBD 
from dbo.AGG_WLY_STR_PROD f with(nolock)
where f.STORE_CODE =2222 and f.DATE_KEY = (select d.WEEK_END_DATE_KEY-7 from dim_date d where d.DATE_FLD = cast(getdate()-1 as date)) and f.SALE_TOT_QTY <> 0
group by case when len(PRODUCT_CODE) < 6 then right('000000'+product_code,6)  else PRODUCT_CODE end

union all

select
    case when len(PRODUCT_CODE) < 6 then right('000000'+product_code,6)  else PRODUCT_CODE end as [Product Code],
    sum(f.SALE_TOT_QTY) as UnitsSold,
    'AMLLW2' as SalesBD 
from dbo.AGG_WLY_STR_PROD f with(nolock)
where f.STORE_CODE =3333 and f.DATE_KEY = (select d.WEEK_END_DATE_KEY-14 from dim_date d where d.DATE_FLD = cast(getdate()-1 as date)) and f.SALE_TOT_QTY <> 0
group by case when len(PRODUCT_CODE) < 6 then right('000000'+product_code,6)  else PRODUCT_CODE end

    union all

select
    case when len(PRODUCT_CODE) < 6 then right('000000'+product_code,6)  else PRODUCT_CODE end as [Product Code],
    sum(f.SALE_TOT_QTY) as UnitsSold,
    'AMLLW3' as SalesBD 
from dbo.AGG_WLY_STR_PROD f with(nolock)
where f.STORE_CODE =4444 and f.DATE_KEY = (select d.WEEK_END_DATE_KEY-21 from dim_date d where d.DATE_FLD = cast(getdate()-1 as date)) and f.SALE_TOT_QTY <> 0
group by case when len(PRODUCT_CODE) < 6 then right('000000'+product_code,6)  else PRODUCT_CODE end

union all

select
    case when len(PRODUCT_CODE) < 6 then right('000000'+product_code,6)  else PRODUCT_CODE end as [Product Code],
    sum(f.SALE_TOT_QTY) as UnitsSold,
    'AMLLW4' as SalesBD 
from dbo.AGG_WLY_STR_PROD f with(nolock)
where f.STORE_CODE =5555 and f.DATE_KEY = (select d.WEEK_END_DATE_KEY-28 from dim_date d where d.DATE_FLD = cast(getdate()-1 as date)) and f.SALE_TOT_QTY <> 0
group by case when len(PRODUCT_CODE) < 6 then right('000000'+product_code,6)  else PRODUCT_CODE end

),adw as
(
select
case when len(PRODUCT_CODE) < 6 then right('000000'+product_code,6) else PRODUCT_CODE end as [Product Code],
sum(f.SALE_TOT_QTY) as UnitsSold,
'ADWYTD' as SalesBD
from dbo.AGG_MLY_STR_PROD f with(nolock)
where f.STORE_CODE=12 and f.DATE_KEY > (select min(date_key) from dim_date where year = (select d.YEAR from dim_date d where d.DATE_FLD = cast(getdate()-1 as date))) and f.SALE_TOT_QTY <> 0
group by case when len(PRODUCT_CODE) < 6 then right('000000'+product_code,6) else PRODUCT_CODE end

union all

select
    case when len(PRODUCT_CODE) < 6 then right('000000'+product_code,6)  else PRODUCT_CODE end as [Product Code],
    sum(f.SALE_TOT_QTY) as UnitsSold,
    'ADWWTD' as SalesBD 
from dbo.AGG_WLY_STR_PROD f with(nolock)
where f.STORE_CODE=13 and f.DATE_KEY = (select d.WEEK_END_DATE_KEY from dim_date d where d.DATE_FLD = cast(getdate()-1 as date)) and f.SALE_TOT_QTY <> 0
group by case when len(PRODUCT_CODE) < 6 then right('000000'+product_code,6)  else PRODUCT_CODE end

union all

select
    case when len(PRODUCT_CODE) < 6 then right('000000'+product_code,6)  else PRODUCT_CODE end as [Product Code],
    sum(f.SALE_TOT_QTY) as UnitsSold,
    'ADWLW' as SalesBD 
from dbo.AGG_WLY_STR_PROD f with(nolock)
where f.STORE_CODE=14 and f.DATE_KEY = (select d.WEEK_END_DATE_KEY-7 from dim_date d where d.DATE_FLD = cast(getdate()-1 as date)) and f.SALE_TOT_QTY <> 0
group by case when len(PRODUCT_CODE) < 6 then right('000000'+product_code,6)  else PRODUCT_CODE end

union all

select
    case when len(PRODUCT_CODE) < 6 then right('000000'+product_code,6)  else PRODUCT_CODE end as [Product Code],
    sum(f.SALE_TOT_QTY) as UnitsSold,
    'ADWLW2' as SalesBD 
from dbo.AGG_WLY_STR_PROD f with(nolock)
where f.STORE_CODE=15 and f.DATE_KEY = (select d.WEEK_END_DATE_KEY-14 from dim_date d where d.DATE_FLD = cast(getdate()-1 as date)) and f.SALE_TOT_QTY <> 0
group by case when len(PRODUCT_CODE) < 6 then right('000000'+product_code,6)  else PRODUCT_CODE end

    union all

select
    case when len(PRODUCT_CODE) < 6 then right('000000'+product_code,6)  else PRODUCT_CODE end as [Product Code],
    sum(f.SALE_TOT_QTY) as UnitsSold,
    'ADWLW3' as SalesBD 
from dbo.AGG_WLY_STR_PROD f with(nolock)
where f.STORE_CODE=16 and f.DATE_KEY = (select d.WEEK_END_DATE_KEY-21 from dim_date d where d.DATE_FLD = cast(getdate()-1 as date)) and f.SALE_TOT_QTY <> 0
group by case when len(PRODUCT_CODE) < 6 then right('000000'+product_code,6)  else PRODUCT_CODE end

union all

select
    case when len(PRODUCT_CODE) < 6 then right('000000'+product_code,6)  else PRODUCT_CODE end as [Product Code],
    sum(f.SALE_TOT_QTY) as UnitsSold,
    'ADWLW4' as SalesBD 
from dbo.AGG_WLY_STR_PROD f with(nolock)
where f.STORE_CODE=17 and f.DATE_KEY = (select d.WEEK_END_DATE_KEY-28 from dim_date d where d.DATE_FLD = cast(getdate()-1 as date)) and f.SALE_TOT_QTY <> 0
group by case when len(PRODUCT_CODE) < 6 then right('000000'+product_code,6)  else PRODUCT_CODE end

)

select [product code],cast(isnull([AMLStoresYTD],0) as int) as [AMLStoresYTD]
,cast(isnull([AMLStoresWTD],0) as int) as [AMLStoresWTD]
,cast(isnull([AMLStoresLW],0) as int) as [AMLStoresLW]
,cast(isnull([AMLStoresLW2],0) as int) as [AMLStoresLW2]
,cast(isnull([AMLStoresLW3],0) as int) as [AMLStoresLW3]
,cast(isnull([AMLStoresLW4],0) as int) as [AMLStoresLW4]
into #AML
from
(
select [product code],
UnitsSold,
SalesBD
from AML
--group by [Product Code],SalesBD
)as pt
pivot(max(unitssold)
for SalesBD in([AMLStoresYTD],[AMLStoresWTD],[AMLStoresLW],[AMLStoresLW2],[AMLStoresLW3],[AMLStoresLW4]))pivottable

union all

select [product code],cast(isnull([ADWYTD],0) as int) as [ADWYTD]
,cast(isnull([ADWWTD],0) as int) as [ADWWTD]
,cast(isnull([ADWLW],0) as int) as [ADWLW]
,cast(isnull([ADWLW2],0) as int) as [ADWLW2]
,cast(isnull([ADWLW3],0) as int) as [ADWLW3]
,cast(isnull([ADWLW4],0) as int) as [ADWLW4]
into #ADW
from
(
select [product code],
UnitsSold,
SalesBD
from adw
--group by [Product Code],SalesBD
)as pt
pivot(max(unitssold)
for SalesBD in([ADWYTD],[ADWWTD],[ADWLW],[ADWLW2],[ADWLW3],[ADWLW4]))pivottab

select * from #aml
select * from #adw

I am not sure whether i can create two separate temp table or not.
This is just a part of my code . If this approach works then i have 5 more to add .

Any advise can help

Regards
Farhan Jamil