如何把sqlserver中的列转换成行，行转换成列，显示。

概述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 ('张三','

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),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,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 英语   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,jIDu='Q1',xiaoshou=(select Q1 from test1 where ID=a.ID)  from test1 as a  union  select ID,jIDu='Q2',xiaoshou=(select Q2 from test1 where ID=a.ID)  from test1 as a  union  select ID,jIDu='Q3',xiaoshou=(select Q3 from test1 where ID=a.ID)  from test1 as a  union  select ID,jIDu='Q4',xiaoshou=(select Q4 from test1 where ID=a.ID)  from test1 as a  

总结

以上是内存溢出为你收集整理的如何把sqlserver中的列转换成行，行转换成列，显示。全部内容，希望文章能够帮你解决如何把sqlserver中的列转换成行，行转换成列，显示。所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。