例一:列轉行
drop table test
create table test (name char(10),km char(10),cj int)
go
insert test values(''張三'',''語文'',80)
insert test values(''張三'',''數學'',86)
insert test values(''張三'',''英語'',75)
insert test values(''李四'',''語文'',78)
insert test values(''李四'',''數學'',85)
insert test values(''李四'',''英語'',78)
select * from test
想變成
姓名 語文 數學 英語
張三 80 86 75
李四 78 85 78
動態的:
declare @sql varchar(8000)
set @sql = ''select name''
select @sql = @sql + '',sum(case km when ''''''+km+'''''' then cj end) [''+km+'']''
from (select distinct km from test) as a
select @sql = @sql+'' from test group by name''
print @sql
exec(@sql)
靜態的:
select name,
sum(case km when ''數學 '' then cj end) [數學 ],
sum(case km when ''英語 '' then cj end) [英語 ],
sum(case km when ''語文 '' then cj end) [語文 ]
from test group by name
例二:列轉行
drop table t
create table t (cardno int,sex varchar(2),age int)
insert into t values (1,''男'',12)
insert into t values (2,''男'',10)
insert into t values (3,''女'',14)
insert into t values (4,''女'',18)
insert into t values (5,''男'',20)
select * from t
動態的:
declare @sql varchar(8000)
set @sql=''select a.id ''
select @sql=@sql + '',sum(case a.sex when ''''''+ a.sex +'''''' then a.age end) [''+ a.sex+'']''
from (select 1 as id,sex, avg(age) age from t group by sex) as a
select @sql = @sql + '' from (select 1 as id,sex,avg(age)age from t group by sex) as a group by a.id''
print @sql
exec (@sql)
靜態的:
select
sum(case a.sex when ''男'' then a.age end) [男],
sum(case a.sex when ''女'' then a.age end) [女]
from (select 1 as id,sex,avg(age)age from t group by sex) as a
group by a.id
例三:行轉列
drop table t1
create table t1 (a int,b int,c int,d int,e int,f int,g int,
