有以下資料源：
以下是我寫的腳本陳述句：
;with cta as (
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
),
ctb as (
select notena,sum(TranNum) as tranval from Noteinf_Detail group by notena
)
select a.*
,b.tranval
from cta as a
left join ctb as b on a.notena = b.notena
是能實作要的需求，但是否可以將兩個子集，寫成一個集？
因為行轉列是動態的，發生幾個月，就有幾列，不是寫成靜態。
uj5u.com熱心網友回復：
參照https://bbs.csdn.net/topics/240002706
uj5u.com熱心網友回復：
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='select [notena]'+@Sql+',sum(TranNum) as tranval FROM [Noteinf_Detail] group by [notena]'
EXEC(@Sql)
uj5u.com熱心網友回復：
我仔細研究了一下，你給我的陳述句結構，這個方法，不能適應我這個需求，我是直接改造成這樣的結構：
select a.*,tranval
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 (
select notena,sum(TranNum) as tranval from Noteinf_Detail group by notena ) as b on a.notena =b.notena
主要是sum是聚合函式，直接植入在pivot 這個子集中，我測驗執行不能成功。
放在里層，還是外層，都不支持啊。我不明白是不是我寫的有問題。
如果放在外層，里層又沒有呼叫這個欄位
比如這樣：
select *
,tranval = (select notena,sum(TranNum) as tranval as b from Noteinf_Detail where a.notena =b.notena group by notena )
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
uj5u.com熱心網友回復：
用pivot需要先匯總uj5u.com熱心網友回復：
動態拼接SQL，參照上次連接把這一段生成([2020-01], [2020-02], [2020-03], [2020-04], [2020-05], [2020-06], [2020-07], [2020-08], [2020-09]
,[2020-10])
e.g.
SELECT *
FROM (SELECT [notena]
,[mCour]
,[TolBottle]
,SUM(TranNum) OVER (PARTITION BY [notena]) AS tranval
FROM [Noteinf_Detail]) AS t1
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;
uj5u.com熱心網友回復：
學習的帖子，標記一下。經常過來學習。轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/165450.html
標籤：應用實例
上一篇：求大神Idea怎么用快捷方式創建class與page檔案？Eclipse是ctrl+n
下一篇：一道資料庫題