Dynamic procession transfers-CodePudding

Has the following data table:

I need to data processing into the following form:

Including notena paper name, number of TranNum paper pen,
I write code like this:

 
; With a0 as (
The select notena 
, the sum (TranNum) as tranval 
The from Noteinf_Detail 
Group by notena 
), 
The cta as (
Select the 'number' as [paper name] 
, the sum (case when notena='purchase order' then tranval else 0 end) as/purchase order 

, the sum (case when notena='scheduling single then tranval else 0 end) as [scheduling list] 
, the sum (case when notena='receipt to sign for' then tranval else 0 end) as [the receipt receipt] 
, the sum (case when notena='internal distribution list then tranval else 0 end) as internal distribution of single] [
, the sum (case when notena='distribution application form then tranval else 0 end) as] [distribution apps 
, the sum (case when notena='purchase requisition then tranval else 0 end) as (purchase requisition) 
, the sum (case when notena='production plan list then tranval else 0 end) as single] [production plan 
, the sum (case when notena='production order then tranval else 0 end) as [order] 
, the sum (case when notena='sales order - logistics' then tranval else 0 end) as [sales order - logistics] 
, the sum (case when notena='sales order - business then tranval else 0 end) as [sales order - business] 
, the sum (case when notena='transfer list then tranval else 0 end) as [transfer list] 

The from a0 
) 
, b0 as (
The select notena mCour, TolBottle from Noteinf_Detail 
), 
CTB as (
Select * from b0 
Pivot (Max (TolBottle) for mCour (in 2020-01], [[2020-02], [2020-03], [2020-04], [2020-05], [2020-06], [2020-07], [2020-08], [2020-09], [2020-10], [2020-11], [2020-12]) 
) as PVT 
) 
- select * from cta

Just can't together, can you tell me what to achieve more efficient concise form

CodePudding user response:

Is missing in the data, TolBottle, and another TolQty filter doesn't need, thank you

CodePudding user response:

Your desired goals in the screenshot is not according to the date line column, the ultimate goal is to note name in this column shows?

CodePudding user response:

refer to the second floor RINK_1 response:

the expectations of the target in the screenshot you didn't do turn column by date, the ultimate goal is to note name in this column shows?

Yes, yes... That is the thing,

CodePudding user response:

reference morliz son hin reply: 3/f

Quote: refer to the second floor RINK_1 response:
the expectations of the target in the screenshot you didn't do turn column by date, the ultimate goal is to note name in this column shows?

Yes, yes... Is the way it is,

Try the following, never measure,

 
Select * 
The from 
(select mCour notena, TolBottle 
The from Noteinf_Detail 
Union all 
Select the 'number', notena, sum (TranNum) 
The from Noteinf_Detail 
As A group by notena) 
Pivot (Max (tolbottle) for notena (in [the purchase order], [scheduling list], [the receipt receipt], [internal distribution list], [] distribution apps, [purchase requisition], [production plan list], [order], [sales order - logistics], [sales order - business], [transfer])) as B