select years,(select amount from Tb_Amount as A where month=1 and A.years=Tb_Amount.years)as m1,(select amount from Tb_Amount as A where month=2 and A.years=Tb_Amount.years)as m2,(select amount from Tb_Amount as A where month=3 and A.years=Tb_Amount.years)as m3from Tb_Amount group by years

select years as 年份,sum(case when month='1' then amount end) as 一月, sum(case when month='2' then amount end) as 二月,sum(case when month='3' then amount end) as 三月from dbo.Tb_Amount group by years order by years desc

select uname as 姓名,SUM(case when era='年輕' then amount end) as 年輕,SUM(case when era='中年' then amount end) as 中年,SUM(case when era='老年' then amount end) as 老年from Tb_People group by uname order by uname desc

select sname as 姓名,max(case Subject when '語文' then grade else 0 end) as 語文,max(case Subject when '數學' then grade else 0 end) as 數學,max(case Subject when '英語' then grade else 0 end) as 英語from dbo.Tb_Student group by sname order by sname desc

declare @sql varchar(8000)set @sql = 'select sname as ' + '姓名'select @sql = @sql + ' , max(case Subject when ''' + Subject + ''' then grade else 0 end) [' + Subject + ']'from (select distinct Subject from Tb_Student) as aset @sql = @sql + ' from Tb_Student group by sname order by sname desc'exec(@sql)

select t.sname AS 姓名,sum(decode(t.subject,'語文',grade,null))語文 ,sum(decode(t.subject,'數學',grade,null)) 數學,sum(decode(t.subject,'英語',grade,null)) 英語from Tb_Student t group by sname order by sname desc

select *from (select sname,[Course ] ='數學',[Score]=[數學] from Tb_students union allselect sname,[Course]='英語',[Score]=[英語] from Tb_students union allselect sname,[Course]='語文',[Score]=[語文] from Tb_students)torder by sname,case [Course] when '語文' then 1 when '數學' then 2 when '英語' then 3 endgo --列轉行的靜態方案：UNPIVOT,sql2005及以后版本  SELECT sname,Subject, grade from dbo.Tb_students unpivot(grade for Subject in([語文],[數學],[英語]))as up GO   --列轉行的動態方案：UNPIVOT,sql2005及以后版本 --因為行是動態所以這里就從INFORMATION_SCHEMA.COLUMNS視圖中獲取列來構造行，同樣也使用了XML處理。 declare @s nvarchar(4000)select @s=isnull(@s+',','')+quotename(Name)from syscolumns where ID=object_id('Tb_students') and Name not in('sname')order by Colidexec('select sname,[Subject],[grade] from Tb_students unpivot ([grade] for [Subject] in('+@s+'))b')goselect  sname,[Subject],[grade]from  Tb_studentsunpivot  ([grade] for [Subject] in([數學],[英語],[語文]))b