有以下資料源表：
我需要將資料處理成如下形式：
其中notena票據名稱 ，TranNum票據筆數。
我寫的代碼是這樣的：
;with a0 as (
select notena
,sum(TranNum) as tranval
from Noteinf_Detail
group by notena
),
cta as (
select '筆數' as [票據名稱]
,sum(case when notena ='采購訂單' then tranval else 0 end ) as [采購訂單]
,sum(case when notena ='調度單' then tranval else 0 end ) as [調度單]
,sum(case when notena ='回單簽收' then tranval else 0 end ) as [回單簽收]
,sum(case when notena ='內部配送單' then tranval else 0 end ) as [內部配送單]
,sum(case when notena ='配送申請單' then tranval else 0 end ) as [配送申請單]
,sum(case when notena ='請購單' then tranval else 0 end ) as [請購單]
,sum(case when notena ='生產計劃單' then tranval else 0 end ) as [生產計劃單]
,sum(case when notena ='生產通知單' then tranval else 0 end ) as [生產通知單]
,sum(case when notena ='銷售訂單-物流' then tranval else 0 end ) as [銷售訂單-物流]
,sum(case when notena ='銷售訂單-業務' then tranval else 0 end ) as [銷售訂單-業務]
,sum(case when notena ='轉運單' then tranval else 0 end ) as [轉運單]
from a0
)
,b0 as (
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
就是沒法合并在一起，請問有什么更高效簡潔的形式來實作
uj5u.com熱心網友回復：
遺漏了月份中的資料，是TolBottle，而另一個TolQty過濾不需要。感謝uj5u.com熱心網友回復：
你的期望目標截圖里并沒有按日期來行轉列，最終目標是票據名稱這一列里顯示月份？uj5u.com熱心網友回復：
對，對……是這么回事。
uj5u.com熱心網友回復：
試試下面的，沒測過。
select *
from
(select mCour,notena,TolBottle
from Noteinf_Detail
union all
select '筆數',notena,sum(TranNum)
from Noteinf_Detail
group by notena) as A
pivot (max(tolbottle) for notena in ([采購訂單],[調度單],[回單簽收],[內部配送單],[配送申請單],[請購單],[生產計劃單],[生產通知單],[銷售訂單-物流],[銷售訂單-業務],[轉運單])) as B
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/170181.html
標籤：應用實例
上一篇：資料庫的基本性質
下一篇：為什么在使用SQL2008的“匯入匯出資料”功能，將一個SQL資料庫中的表匯入到另一個SQL資料庫時出錯？如何解決？