Has the following data sources:


The following is the script I wrote the statement:

 
; With the cta as (
Select * from (
Select [notena] 
, [mCour] 
, [TolBottle] 
The from [Noteinf_Detail] 
) p 
The 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]) 
) as PVT 
), 
CTB as (
The select notena, sum (TranNum) as tranval from Noteinf_Detail group by notena 
) 
Select a. * 
, b.t ranval 

The from the cta as a 
Left the join CTB as b on a.n otena=b.n otena 

Is can realize to demand, but whether two subsets, can be written as a set?
Because line column is dynamic, in a few months, there are a few columns, not static,

CodePudding user response:

With reference to the
https://bbs.csdn.net/topics/240002706

CodePudding user response:

E.g.

 DECLARE @ Sql NVARCHAR (4000)='
SELECT @ Sql +=', Max (case when [mCour]=' ' '+ RTRIM ([mCour]) +' ' 'then [TolBottle] END) AS' + QUOTENAME ([mCour]) FROM [Noteinf_Detail] GROUP BY [mCour] 
SET @ Sql='the select [notena] Sql + + @', the sum (TranNum) as tranval FROM [Noteinf_Detail] group by [notena] '
EXEC (Sql) @ 

CodePudding user response:

I carefully studied the once, you gave me the sentence structure, this method, which can not adapt to the demand, I I is directly converted into this structure:

 
Select a. *, tranval 

The from (
Select * from (
Select [notena], [mCour], [TolBottle] from [Noteinf_Detail] 
) p 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])) as PVT 
) as a 
Inner join (
The select notena, sum (TranNum) as tranval from Noteinf_Detail group by notena) as b on a.n otena=b.n otena 

Mainly is the sum is the aggregation function, implanted directly on the pivot of this subset, I can't successful test execution,
On the inner or outer layer, are not support ah, I don't know whether I write has a problem,

If in the outer layer, the layer and didn't call the field
Like this:

 
Select * 
And tranval=(select notena, sum (TranNum) as tranval as b from Noteinf_Detail where a.n otena=b.n otena group by notena) 

The from (
Select [notena], [mCour], [TolBottle] from [Noteinf_Detail] 
) p 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])) as PVT 

CodePudding user response:

Use the pivot need to summary first

CodePudding user response:

Dynamic splicing SQL, with reference to the last link the

 generated from this period (2020-01], [[2020-02], [2020-03], [2020-04], [2020-05], [2020-06], [2020-07], [2020-08], [2020-09] 
, the 2020-10) 

E.g.

 SELECT * 
The FROM (SELECT [notena] 
, [mCour] 
, [TolBottle] 
, the SUM (TranNum) OVER (PARTITION BY [notena]) AS tranval 
The FROM [Noteinf_Detail]) AS t1 
The PIVOT (MAX ([TolBottle]) 
FOR [mCour] IN [[2020-01], [2020-02], [2020-03], [2020-04], [2020-05], [2020-06], [2020-07], [2020-08], [2020-09] 
, the 2020-10)) AS PVT;