SQL 数据库中行转列，和列转行

行转列：

原始表：

  create table sales( Year   int,   Queryter   int, Resultes  int) 
 insert into sales values (2004,1,20)
 insert into sales values(2004,2,30)
 insert into sales values(2004,3,40)
 insert into sales values (2005,1,25)
 insert into sales values (2005,2,35)
 insert into sales values (2005,3,45)

要转化成的表的形式为：

create table sales (Year int,Q1 int ,Q2 int , Q3 int)

 insert  into sales values(2004,20,30,40)

insert into sales values(2005,25,35,45)

 

实现上面转化的SQL语句是：   

  select Year ,max(case when Queryter=1 then Resultes end) as Q1,

        max (case when Queryter=2 then Resultes end) as Q2,

        max(case when Queryter=3 then   Resultes end)as Q3

     from  sales group by Year

列转成行：

   原始表：

create table salesAgg(Year int,Q1 int ,Q2 int , Q3 int)

 insert  into salesAgg values(2004,20,30,40)

insert into salesAgg values(2005,25,35,45)

需要变成的显示表为：

create table salesAgg( Year   int,   Queryter   int, Resultes  int) 
 insert into sales values (2004,1,20)
 insert into sales values(2004,2,30)
 insert into sales values(2004,3,40)
 insert into sales values (2005,1,25)
 insert into sales values (2005,2,35)
 insert into sales values (2005,3,45)

 

需要使用的sql语句：  select * from (
                 select Year,Queryter='1',Resultes=Q1 from salesAgg
                  union all
                 select Year,Queryter='2',Resultes=Q2from salesAgg union all
                 select Year,Queryter='3',Resultes=Q3from salesAgg
) t
 order by Year,Queryter