請問大大們,我要如何將以下收尋結果合併而不會相加在一起,UNION不適用,求解!?
SELECT cCorpIdn,cVendorName,AVG(fraction)as 'Q1' FROM cs.ScoreC where cMonth BETWEEN '01' AND '03' GROUP BY cCorpIdn,cVendorName
SELECT cCorpIdn,cVendorName,AVG(fraction)as 'Q2' FROM cs.ScoreC where cMonth BETWEEN '04' AND '05' GROUP BY cCorpIdn,cVendorName
SELECT cCorpIdn,cVendorName,AVG(fraction)as 'Q3' FROM cs.ScoreC where cMonth BETWEEN '07' AND '09' GROUP BY cCorpIdn,cVendorName
SELECT cCorpIdn,cVendorName,AVG(fraction)as 'Q4' FROM cs.ScoreC where cMonth BETWEEN '10' AND '11' GROUP BY cCorpIdn,cVendorName
合併後再與
SELECT
cCorpIdn,cVendorName,
[01] as '一月', [02] as '二月', [03] as '三月', [04] as '四月',
[05] as '五月', [06] as '六月', [07] as '七月', [08] as '八月',
[09] as '九月', [10] as '十月', [11] as '十一月', [12] as '十二月'
FROM (
SELECT cCorpIdn,cVendorName,cYear, cMonth, fraction
FROM cs.ScoreC
GROUP BY cCorpIdn,cVendorName,cYear, cMonth, fraction
) as GroupTable
PIVOT
(
Sum(fraction)
FOR cMonth IN ([01], [02], [03], [04], [05], [06], [07], [08], [09], [10], [11], [12])
) AS PivotTable
做合併,最後資料顯是希望如下
我覺得應該可以這麼寫.
SELECT cCorpIdn,cVendorName
,SUM(CASE WHEN cMonth = '01' THEN fraction ELSE NULL END) as '一月'
,SUM(CASE WHEN cMonth = '02' THEN fraction ELSE NULL END) as '二月'
..
..
,AVG(CASE WHEN cMonth BETWEEN '01' AND '03' THEN fraction ELSE NULL END)as 'Q1'
,AVG(CASE WHEN cMonth BETWEEN '04' AND '06' THEN fraction ELSE NULL END)as 'Q2'
,AVG(CASE WHEN cMonth BETWEEN '07' AND '09' THEN fraction ELSE NULL END)as 'Q3'
,AVG(CASE WHEN cMonth BETWEEN '10' AND '12' THEN fraction ELSE NULL END)as 'Q4'
FROM cs.ScoreC
where cYear='2019'
GROUP BY cCorpIdn,cVendorName
沒得測試,有錯誤的話請自行修正,用子表SELECT的結果做欄位即可
結果應該是
cCorpIdnA,cVendorNameA,Q1,Q2,Q3,Q4
cCorpIdnB,cVendorNameB,Q1,Q2,Q3,Q4
cCorpIdnC,cVendorNameC,Q1,Q2,Q3,Q4
SELECT
cCorpIdn,cVendorName,
(SELECT ISNULL(AVG(fraction),0)
FROM cs.ScoreC as q1t
where q1t.cMonth BETWEEN '01' AND '03'
AND q1t.cCorpIdn=MT.cCorpIdn) AS 'Q1',
(SELECT ISNULL(AVG(fraction),0)
FROM cs.ScoreC as q2t
where q2t.cMonth BETWEEN '04' AND '06'
AND q2t.cCorpIdn=MT.cCorpIdn) AS 'Q2',
(SELECT ISNULL(AVG(fraction),0)
FROM cs.ScoreC as q3t
where q3t.cMonth BETWEEN '07' AND '09'
AND q3t.cCorpIdn=MT.cCorpIdn) AS 'Q3',
(SELECT ISNULL(AVG(fraction),0)
FROM cs.ScoreC as q4t
where q4t.cMonth BETWEEN '10' AND '12'
AND q4t.cCorpIdn=MT.cCorpIdn) AS 'Q4',
FROM cs.ScoreC AS MT
GROUP BY cCorpIdn,cVendorName