Share via

need help in a sql query

Farhan Jamil 421 Reputation points
2020-11-23T12:20:49.473+00:00

Hi Guys

New to sql. I need to show my final output in this manner . Not sure how to do use pivot or unpivot to get my output in the screenshot shared.
41885-image.png

So far I have my sql query using cte

;with stores
as(

select
case when s.SECTOR like 'MatArea%' then 'Mat'
when s.Store_code = 867 then 'Mat_Web'
when s.store_code between 1001 and 1049 then 'MCO'
when s.store_code between 1698 and 1898 then 'DB'
when s.STORE_CODE = 1899 then 'Db_Web'
when s.STORE_CODE<200 then 'TE UK'
when s.store_code between 1051 and 1299 then 'HB'
when s.store_code between 1301 and 1599 then 'MAS'
when s.store_code between 402 and 498 then 'PLY'
else 'SOH_Other' End as 'Channels'
from dbo.AGG_WLY_STR F with (nolock)
join dbo.dim_store s with (nolock) on s.STORE_CODE = f.store_code
)

,sales as
(
select
a.STORE_CODE
,sale_val as [TY LW Sales]
,sale_val_ly as [LY LW Sales]
,(INV_ALLOCATED_STOCK_VAL + INV_GIT_VAL) as [TY_TW_SOH_Cost_Curl]
,(INV_ALLOCATED_STOCK_VAL_LY + INV_GIT_VAL_LY) as [LY_TW_SOH_Cost_Curl]
,(INV_CLOSING_VAL_AT_PP + INV_GIT_VAL + INV_ALLOCATED_STOCK_VAL) as [TY_TW_SOH_Cost]
,(INV_CLOSING_VAL_AT_PP_LY + INV_GIT_VAL_LY + INV_ALLOCATED_STOCK_VAL_LY) as [LY_TW_SOH_Cost]
,(inv_closing_val_at_hosp + inv_git_val_sp + INV_ALLOCATED_STOCK_VAL_SP) as [TY_TW_SOH_Retail]
,(INV_CLOSING_VAL_AT_HOSP_LY + INV_GIT_VAL_SP_LY + INV_ALLOCATED_STOCK_VAL_SP_LY) as [LY_TW_SOH_Retail]

from dbo.AGG_WLY_STR a with(nolock)
join
dbo.dim_store s with (nolock) on s.STORE_CODE = s.store_code
)

Any help will be appreciated

Regards
Farhan

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,639 questions
0 comments
Accepted answer
  1. Farhan Jamil 421 Reputation points
    2020-11-24T09:44:03.42+00:00

    Hi Both

    Thanks for your help. Only one issue which I can highlight in both of your query that the output i can see is breakdown rather than an aggregate. Sorry, maybe I haven't mentioned in the requirement while I posted the question out.

    However I have solved it myself by modifying one of the queries above .This is what worked for me and what i actually wanted. I agree I dont need cte and no pivot is required. However as I started by using cte, I proceeded with this appraoch.

    ;with stores
    as(

    select s.store_code,
    case when s.SECTOR like 'MatArea%' then 'Mat'
    when s.Store_code = 867 then 'Mat_Web'
    when s.store_code between 1001 and 1049 then 'MCO'
    when s.store_code between 1698 and 1898 then 'Db'
    when s.STORE_CODE = 1899 then 'Db_Web'
    when s.STORE_CODE<200 then 'TE UK'
    when s.store_code between 1051 and 1299 then 'Hb'
    when s.store_code between 1301 and 1599 then 'Mas'
    when s.store_code between 402 and 498 then 'Ply'
    else 'Other' End as 'Channels'
    from dbo.dim_store s with (nolock)
    --order by cast(s.store_code as int) asc
    )
    ,sales_report as
    (
    select
    s.Channels
    ,sum(sale_val) as [TY LW Sales]
    ,sum(sale_val_ly) as [LY LW Sales]
    ,sum((INV_ALLOCATED_STOCK_VAL + INV_GIT_VAL)) as [TY_TW_SOH_Cost_Curl]
    ,sum(INV_ALLOCATED_STOCK_VAL_LY + INV_GIT_VAL_LY) as [LY_TW_SOH_Cost_Curl]
    ,sum(INV_CLOSING_VAL_AT_PP + INV_GIT_VAL + INV_ALLOCATED_STOCK_VAL) as [TY_TW_SOH_Cost]
    ,sum(INV_CLOSING_VAL_AT_PP_LY + INV_GIT_VAL_LY + INV_ALLOCATED_STOCK_VAL_LY) as [LY_TW_SOH_Cost]
    ,sum(inv_closing_val_at_hosp + inv_git_val_sp + INV_ALLOCATED_STOCK_VAL_SP) as [TY_TW_SOH_Retail]
    ,sum(INV_CLOSING_VAL_AT_HOSP_LY + INV_GIT_VAL_SP_LY + INV_ALLOCATED_STOCK_VAL_SP_LY) as [LY_TW_SOH_Retail]

    from dbo.AGG_WLY_STR a with(nolock)
    join
    stores s with (nolock) on a.STORE_CODE = s.store_code
    and s.Channels<>'Other'
    where a.DATE_KEY = (select max(date_key)-7 from dbo.AGG_WLY_STR with(nolock))
    group by Channels
    )

    select * from sales

    0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Guoxiong 8,206 Reputation points
    2020-11-23T15:51:25.92+00:00

    I do not think you need the CTE stores. Just move the CASE statement to the CTE sales:

    ;with sales as
(
    select
        case when s.SECTOR like 'MatArea%' then 'Mat'
             when s.Store_code = 867 then 'Mat_Web'
             when s.store_code between 1001 and 1049 then 'MCO'
             when s.store_code between 1698 and 1898 then 'DB'
             when s.STORE_CODE = 1899 then 'Db_Web'
             when s.STORE_CODE<200 then 'TE UK'
             when s.store_code between 1051 and 1299 then 'HB'
             when s.store_code between 1301 and 1599 then 'MAS'
             when s.store_code between 402 and 498 then 'PLY'
             else 'SOH_Other' 
        End as 'Channels'
        ,sale_val as [TY LW Sales]
        ,sale_val_ly as [LY LW Sales]
        ,(INV_ALLOCATED_STOCK_VAL + INV_GIT_VAL) as [TY_TW_SOH_Cost_Curl]
        ,(INV_ALLOCATED_STOCK_VAL_LY + INV_GIT_VAL_LY) as [LY_TW_SOH_Cost_Curl]
        ,(INV_CLOSING_VAL_AT_PP + INV_GIT_VAL + INV_ALLOCATED_STOCK_VAL) as [TY_TW_SOH_Cost]
        ,(INV_CLOSING_VAL_AT_PP_LY + INV_GIT_VAL_LY + INV_ALLOCATED_STOCK_VAL_LY) as [LY_TW_SOH_Cost]
        ,(inv_closing_val_at_hosp + inv_git_val_sp + INV_ALLOCATED_STOCK_VAL_SP) as [TY_TW_SOH_Retail]
        ,(INV_CLOSING_VAL_AT_HOSP_LY + INV_GIT_VAL_SP_LY + INV_ALLOCATED_STOCK_VAL_SP_LY) as [LY_TW_SOH_Retail]
    from dbo.AGG_WLY_STR a with(nolock)
    join dbo.dim_store s with (nolock) on s.STORE_CODE = s.store_code
)

SELECT * FROM sales;
  2. MelissaMa-MSFT 24,196 Reputation points
    2020-11-24T01:52:50.263+00:00

    Hi @Farhan Jamil ,

    Agreed with Guoxiong, there is no need for you to use pivot or unpivot in your case.

    You could even remove the CTE part like below: 

    select  
case when s.SECTOR like 'MatArea%' then 'Mat'  
when s.Store_code = 867 then 'Mat_Web'  
when s.store_code between 1001 and 1049 then 'MCO'  
when s.store_code between 1698 and 1898 then 'DB'  
when s.STORE_CODE = 1899 then 'Db_Web'  
when s.STORE_CODE<200 then 'TE UK'  
when s.store_code between 1051 and 1299 then 'HB'  
when s.store_code between 1301 and 1599 then 'MAS'  
when s.store_code between 402 and 498 then 'PLY'  
else 'SOH_Other' End as 'Channels'  
,sale_val as [TY LW Sales]  
,sale_val_ly as [LY LW Sales]  
,(INV_ALLOCATED_STOCK_VAL + INV_GIT_VAL) as [TY_TW_SOH_Cost_Curl]  
,(INV_ALLOCATED_STOCK_VAL_LY + INV_GIT_VAL_LY) as [LY_TW_SOH_Cost_Curl]  
,(INV_CLOSING_VAL_AT_PP + INV_GIT_VAL + INV_ALLOCATED_STOCK_VAL) as [TY_TW_SOH_Cost]  
,(INV_CLOSING_VAL_AT_PP_LY + INV_GIT_VAL_LY + INV_ALLOCATED_STOCK_VAL_LY) as [LY_TW_SOH_Cost]  
,(inv_closing_val_at_hosp + inv_git_val_sp + INV_ALLOCATED_STOCK_VAL_SP) as [TY_TW_SOH_Retail]  
,(INV_CLOSING_VAL_AT_HOSP_LY + INV_GIT_VAL_SP_LY + INV_ALLOCATED_STOCK_VAL_SP_LY) as [LY_TW_SOH_Retail]  
from dbo.AGG_WLY_STR a with(nolock)  
join dbo.dim_store s with (nolock) on s.STORE_CODE = s.store_code

    Or you could still make some modifications on your ctes as below: 

    ;with stores  
as(  
select s.SECTOR,s.Store_code,  
case when s.SECTOR like 'MatArea%' then 'Mat'  
when s.Store_code = 867 then 'Mat_Web'  
when s.store_code between 1001 and 1049 then 'MCO'  
when s.store_code between 1698 and 1898 then 'DB'  
when s.STORE_CODE = 1899 then 'Db_Web'  
when s.STORE_CODE<200 then 'TE UK'  
when s.store_code between 1051 and 1299 then 'HB'  
when s.store_code between 1301 and 1599 then 'MAS'  
when s.store_code between 402 and 498 then 'PLY'  
else 'SOH_Other' End as 'Channels'  
from dbo.AGG_WLY_STR F with (nolock)  
join dbo.dim_store s with (nolock) on s.STORE_CODE = f.store_code  
)  
,sales as  
(  
select  
a.STORE_CODE,s.SECTOR  
,sale_val as [TY LW Sales]  
,sale_val_ly as [LY LW Sales]  
,(INV_ALLOCATED_STOCK_VAL + INV_GIT_VAL) as [TY_TW_SOH_Cost_Curl]  
,(INV_ALLOCATED_STOCK_VAL_LY + INV_GIT_VAL_LY) as [LY_TW_SOH_Cost_Curl]  
,(INV_CLOSING_VAL_AT_PP + INV_GIT_VAL + INV_ALLOCATED_STOCK_VAL) as [TY_TW_SOH_Cost]  
,(INV_CLOSING_VAL_AT_PP_LY + INV_GIT_VAL_LY + INV_ALLOCATED_STOCK_VAL_LY) as [LY_TW_SOH_Cost]  
,(inv_closing_val_at_hosp + inv_git_val_sp + INV_ALLOCATED_STOCK_VAL_SP) as [TY_TW_SOH_Retail]  
,(INV_CLOSING_VAL_AT_HOSP_LY + INV_GIT_VAL_SP_LY + INV_ALLOCATED_STOCK_VAL_SP_LY) as [LY_TW_SOH_Retail]  
from dbo.AGG_WLY_STR a with(nolock)  
join dbo.dim_store s with (nolock) on s.STORE_CODE = s.store_code  
)  
  
select a.Channels,[TY LW Sales],[LY LW Sales],[TY_TW_SOH_Cost_Curl]   
,[LY_TW_SOH_Cost_Curl],[LY_TW_SOH_Cost_Curl]  
,[TY_TW_SOH_Cost],[LY_TW_SOH_Cost],[TY_TW_SOH_Retail],[LY_TW_SOH_Retail]  
from stores a  
left join sales b   
on a.Store_code=b.STORE_CODE and a.SECTOR=b.SECTOR

    Best regards
    Melissa

    If the answer is helpful, please click "Accept Answer" and upvote it.
    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.
    Hot issues November--What can I do if my transaction log is full?
    Hot issues November--How to convert Profiler trace into a SQL Server table

    0 comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.