简单有用的SQL脚本 (行列互转,查询一个表内相同纪录等)
前端之家收集整理的这篇文章主要介绍了
简单有用的SQL脚本 (行列互转,查询一个表内相同纪录等)，
前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。
行列互转
<div class="codetitle"><a style="CURSOR: pointer" data="25237" class="copybut" id="copybut25237" onclick="doCopy('code25237')"> 代码如下:
<div class="codebody" id="code25237">
create table test(id int,name varchar(20),quarter int,profile int)
insert into test values(1,'a',1,1000)
insert into test values(1,2,2000)
insert into test values(1,3,4000)
insert into test values(1,4,5000)
insert into test values(2,'b',3000)
insert into test values(2,3500)
insert into test values(2,4200)
insert into test values(2,5500)
select
from test
--行转列
select id,name,
[1] as "一季度",
[2] as "二季度",
[3] as "三季度",
[4] as "四季度",
[5] as "5"
from
test
pivot
(
sum(profile)
for quarter in
([1],[2],[3],[4],[5])
)
as pvt create table test2(id int,Q1 int,Q2 int,Q3 int,Q4 int)
insert into test2 values(1,1000,2000,4000,5000)
insert into test2 values(2,3000,3500,4200,5500)
select from test2
--列转行
select id,quarter,profile
from
test2
unpivot
(
profile
for quarter in
([Q1],[Q2],[Q3],[Q4])
)
as unpvt
