if OBJECT_ID(‘week_income‘) is not nulldrop table week_incomegocreate table week_income(    employee varchar(10),    weekname varchar(10),    income int)goinsert into week_incomeselect ‘張三‘,‘星期一‘,1000 union allselect ‘張三‘,‘星期二‘,2000 union allselect ‘張三‘,‘星期三‘,1500 union allselect ‘張三‘,‘星期四‘,2000 union allselect ‘張三‘,‘星期五‘,3000 union allselect ‘張三‘,‘星期六‘,4000 union allselect ‘張三‘,‘星期日‘,5000 union allselect ‘李四‘,‘星期一‘,1000 union allselect ‘李四‘,‘星期二‘,2000 union allselect ‘李四‘,‘星期三‘,1500 union allselect ‘李四‘,‘星期四‘,2000 union allselect ‘李四‘,‘星期五‘,3000 union allselect ‘李四‘,‘星期六‘,4000 union allselect ‘李四‘,‘星期日‘,8000 select * from week_income

select employee as ‘社員‘      ,sum(case weekname when ‘星期一‘ then income else 0 end) as ‘星期一‘      ,sum(case weekname when ‘星期二‘ then income else 0 end) as ‘星期二‘      ,sum(case weekname when ‘星期三‘ then income else 0 end) as ‘星期三‘      ,sum(case weekname when ‘星期四‘ then income else 0 end) as ‘星期四‘      ,sum(case weekname when ‘星期五‘ then income else 0 end) as ‘星期五‘      ,sum(case weekname when ‘星期六‘ then income else 0 end) as ‘星期六‘      ,sum(case weekname when ‘星期日‘ then income else 0 end) as ‘星期日‘                              from week_incomegroup by employee

SELECT <non-pivoted column>,     [first pivoted column] AS <column name>,     [second pivoted column] AS <column name>,     ...     [last pivoted column] AS <column name> FROM     (<SELECT query that produces the data>)    AS <alias for the source query> PIVOT (     <aggregation function>(<column being aggregated>) FOR [<column that contains the values that will become column headers>]     IN ( [first pivoted column], [second pivoted column],     ... [last pivoted column]) ) AS <alias for the pivot table> <optional ORDER BY clause>;

select employee  as ‘社員‘,[星期一],[星期二],[星期三],[星期四],[星期五],[星期六],[星期日] --step 3from (select employee ,weekname,income from week_income) t -- step 2pivot(　　　　--step 1    sum(income) for weekname in([星期一],[星期二],[星期三],[星期四],[星期五],[星期六],[星期日])) b

select * into tmp from (select employee  as ‘社員‘,[星期一],[星期二],[星期三],[星期四],[星期五],[星期六],[星期日]from (select employee ,weekname,income from week_income) tpivot(    sum(income) for weekname in([星期一],[星期二],[星期三],[星期四],[星期五],[星期六],[星期日])) b) cselect * from tmp 

select 社員 as employee,‘星期一‘ as weekname, 星期一 as income from tmp union allselect 社員 as employee,‘星期二‘ as weekname, 星期二 as income from tmp union allselect 社員 as employee,‘星期三‘ as weekname, 星期三 as income from tmp union allselect 社員 as employee,‘星期四‘ as weekname, 星期四 as income from tmp union allselect 社員 as employee,‘星期五‘ as weekname, 星期五 as income from tmp union allselect 社員 as employee,‘星期六‘ as weekname, 星期六 as income from tmp union allselect 社員 as employee,‘星期日‘ as weekname, 星期日 as income from tmporder by 社員

select 社員 as employee, weekname,incomefrom (select 社員,[星期一],[星期二],[星期三],[星期四],[星期五],[星期六],[星期日] from tmp) aunpivot(    income for weekname in ([星期一],[星期二],[星期三],[星期四],[星期五],[星期六],[星期日] )) b

declare @sql as varchar(4000)set @sql = ‘select employee as ‘‘社員‘‘‘select @sql = @sql + ‘,sum(case weekname when ‘‘‘ + weekname + ‘‘‘ then income else 0 end) as ‘‘‘ + weekname + ‘‘‘‘from (    select distinct weekname from week_income) aset  @sql =@sql + ‘ from week_income    ‘set  @sql =@sql + ‘ group by employee    ‘print @sqlexec (@sql)