行列转换

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]