行列轉換
select Student as '姓名',
max(case Subject when '語文' then Score else 0 end) as '語文' ,--如果這個行是“語文”，就選此行作為列
max(case Subject when '英語' then Score else 0 end ) as '英語'
from Scores
group by Student
order by Student
按月統計
在行列轉換的基礎上，生成1~12月的順序月份。
WITH A AS(
SELECT Month(CreateAt) [Month],ProcessName,State,DATEDIFF(mi,CreateAt,FinishAt) Minutes FROM BPMInstTasks WHERE ExtYear=@Year AND (@ProcessName is NULL OR ProcessName=@ProcessName) AND CreateAt>=@Date1 AND CreateAt<@Date2
),
AA AS(
SELECT State,[Month],Count() Counts,avg(Minutes) AvgMinutes FROM A GROUP BY State,[Month]
),
B AS(
SELECT [Month],
max(case when State='Approved' then Counts else 0 end) Approved,
max(case when State='Rejected' then Counts else 0 end) Rejected,
max(case when State='Running' then Counts else 0 end) Running,
max(case when State='Aborted' then Counts else 0 end) Aborted,
max(case when State='Deleted' then Counts else 0 end) Deleted,
ISNULL(sum(Counts),0) Total,
max(case when State='Approved' then AvgMinutes else 0 end) AvgMinutes
FROM AA GROUP BY [Month]
),
C AS(
SELECT number [Month] FROM MASTER..spt_values WHERE type='P' and number between 1 and 12
),
D AS(
SELECT C.[Month],
ISNULL(B.Approved,0) Approved,
ISNULL(B.Rejected,0) Rejected,
ISNULL(B.Running,0) Running,
ISNULL(B.Aborted,0) Aborted,
ISNULL(B.Deleted,0) Deleted,
ISNULL(B.Total,0) Total,
ISNULL(B.AvgMinutes,0) AvgMinutes
FROM C LEFT JOIN B ON C.[Month]=B.[Month]
)
SELECT FROM D ORDER BY [Month]