首页 > 代码库 > SQL实用
SQL实用
行列互转
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,‘a‘,2,2000)
insert into test values(1,‘a‘,3,4000)
insert into test values(1,‘a‘,4,5000)
insert into test values(2,‘b‘,1,3000)
insert into test values(2,‘b‘,2,3500)
insert into test values(2,‘b‘,3,4200)
insert into test values(2,‘b‘,4,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,name varchar(20), Q1 int, Q2 int, Q3 int, Q4 int)
insert into test2 values(1,‘a‘,1000
insert into test values(1,‘a‘,1,1000)
insert into test values(1,‘a‘,2,2000)
insert into test values(1,‘a‘,3,4000)
insert into test values(1,‘a‘,4,5000)
insert into test values(2,‘b‘,1,3000)
insert into test values(2,‘b‘,2,3500)
insert into test values(2,‘b‘,3,4200)
insert into test values(2,‘b‘,4,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,name varchar(20), Q1 int, Q2 int, Q3 int, Q4 int)
insert into test2 values(1,‘a‘,1000