question

FarhanJamil-5363 avatar image
0 Votes"
FarhanJamil-5363 asked EchoLiu-msft commented

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

sql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

GuoxiongYuan-7218 avatar image
0 Votes"
GuoxiongYuan-7218 answered EchoLiu-msft commented

Replace the CTEs with the table variables @AML and @AWD and then you can use SELECT ... INTO #AML and SELECT ... INTO #AWD. Finally you can UNION both temp tables for your outputs.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


Thanks for your reply GuoxiongYuan-7218 .
I havent tried your solution but i tried an alternative. I created a function and then created recursive temp table . No UNION was required as it solved my objective.

0 Votes 0 ·

Hi @FarhanJamil-5363,

I am glad that your problem has been resolved. You can post your method for everyone to share and accept it as an answer.Your action would be helpful to other users who encounter the same issue and read this thread.

Regards
Echo

0 Votes 0 ·