在Access中可以輕易完成交叉查詢,
可是我在SQL Server都做不出來,
好像沒有那些指令??
高手們是否能助我一臂之力,
我有查在SQL HELP 中輸入 Cross-tab Reports 搜尋後,會有內容教我如何做,但是欄名及資料都要先定義才可交叉查詢,如何將資料變成欄位名的交叉資料???
SELECT P1.*, (P1.Q1 + P1.Q2 + P1.Q3 + P1.Q4) AS YearTotal
FROM (SELECT Year,
SUM(CASE P.Quarter WHEN 1 THEN P.Amount ELSE 0 END) AS Q1,
SUM(CASE P.Quarter WHEN 2 THEN P.Amount ELSE 0 END) AS Q2,
SUM(CASE P.Quarter WHEN 3 THEN P.Amount ELSE 0 END) AS Q3,
SUM(CASE P.Quarter WHEN 4 THEN P.Amount ELSE 0 END) AS Q4
FROM Pivot AS P
GROUP BY P.Year) AS P1
可是我在SQL Server都做不出來,
好像沒有那些指令??
高手們是否能助我一臂之力,
我有查在SQL HELP 中輸入 Cross-tab Reports 搜尋後,會有內容教我如何做,但是欄名及資料都要先定義才可交叉查詢,如何將資料變成欄位名的交叉資料???
SELECT P1.*, (P1.Q1 + P1.Q2 + P1.Q3 + P1.Q4) AS YearTotal
FROM (SELECT Year,
SUM(CASE P.Quarter WHEN 1 THEN P.Amount ELSE 0 END) AS Q1,
SUM(CASE P.Quarter WHEN 2 THEN P.Amount ELSE 0 END) AS Q2,
SUM(CASE P.Quarter WHEN 3 THEN P.Amount ELSE 0 END) AS Q3,
SUM(CASE P.Quarter WHEN 4 THEN P.Amount ELSE 0 END) AS Q4
FROM Pivot AS P
GROUP BY P.Year) AS P1
全站熱搜
留言列表