在数据库中如何让列变成行,让行变成列的问题

时间:2023-01-12 10:32:00
  • create database arron  
  • go  
  • use arron  
  • go  


  • -- createTable init Data  
  • create table students (  
  •   name varchar(25),  
  •   class varchar(25),  
  •   grade int  


  • insert into students values ('张三','语文',20)  
  • insert into students values ('张三','数学',90)  
  • insert into students values ('张三','英语',50)  
  • insert into students values ('李四','语文',81)  
  • insert into students values ('李四','数学',60)  
  • insert into students values ('李四','英语',90)  
  •  


  • -- solution1  
  • select * from students   
  • pivot(   
  • max(grade)   
  • FOR [class] IN ([语文],[数学],[英语])  
  • ) AS pvt  
  •   

  •  
  • -- solution2 相当于自连接  
  •   
  • select A.Name,A.grade as 语文,B.grade as 数学,C.grade as 英语  
  • from students A,students B,students C  
  • where A.Name=B.Name and B.Name=C.Name  
  • and A.class='语文' and B.class='数学'  
  • and C.class='英语'  
  •   
  • -- solution3   
  • select name,  
  • max(case when s.class='语文' then s.grade end) as 语文,  
  • max(case when s.class='数学' then s.grade end) as 数学,  
  • max(case when s.class='英语' then s.grade end) as 英语  
  •  from students s group by name  
  •   
  •   
  • --在有id 的情况下  
  • create table students2 (  
  •   id int primary key identity(1,1),  
  •   name varchar(25),  
  •   class varchar(25),  
  •   grade int  
  • )  
  •   
  • insert into students2 values ('张三','语文',20)  
  • insert into students2 values ('张三','数学',90)  
  • insert into students2 values ('张三','英语',50)  
  • insert into students2 values ('李四','语文',81)  
  • insert into students2 values ('李四','数学',60)  
  • insert into students2 values ('李四','英语',90)  
  •   
  • -- 原先的solution1(有问题)  
  • select * from students2   
  • pivot(   
  • max(grade)   
  • FOR [class] IN ([语文],[数学],[英语])  
  • ) AS pvt  
  • -- 原先的solution2 (ok)  
  • select A.Name,A.grade as 语文,B.grade as 数学,C.grade as 英语  
  • from students A,students2 B,students2 C  
  • where A.Name=B.Name and B.Name=C.Name  
  • and A.class='语文' and B.class='数学'  
  • and C.class='英语'  
  • -- 原先的solution3 (ok)  
  • select name,  
  • max(case when s.class='语文' then s.grade end) as 语文,  
  • max(case when s.class='数学' then s.grade end) as 数学,  
  • max(case when s.class='英语' then s.grade end) as 英语  
  •  from students s group by name  
  •   
  •   
  • --unpivot 函数使用  
  • create table test1(id int,name varchar(20), Q1 int, Q2 int, Q3 int, Q4 int)  
  • insert into test1 values(1,'a',1000,2000,4000,5000)  
  • insert into test1 values(2,'b',3000,3500,4200,5500)   
  •   
  • --实现的sql  
  • select * from test1  
  •   
  • select id ,[name],[jidu],[xiaoshou] from test1  
  • unpivot  
  • (  
  •  xiaoshou for jidu in  
  •  ([q1],[q2],[q3],[q4])  
  • )  
  • as f  
  •   
  • --- 以下的sql 可以替换上面的sql  
  • select id,[name],  
  • jidu='Q1',  
  • xiaoshou=(select Q1 from test1 where id=a.id)  
  • from test1 as a  
  • union  
  • select id,[name],  
  • jidu='Q2',  
  • xiaoshou=(select Q2 from test1 where id=a.id)  
  • from test1 as a  
  • union  
  • select id,[name],  
  • jidu='Q3',  
  • xiaoshou=(select Q3 from test1 where id=a.id)  
  • from test1 as a  
  • union  
  • select id,[name],  
  • jidu='Q4',  
  • xiaoshou=(select Q4 from test1 where id=a.id)  
  • from test1 as a