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

发布时间：2020-12-12 13:58:16 所属栏目：MsSql教程来源：网络整理

导读：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

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,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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!