--马可的,参考!1: 列转为行: eg1: 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)想变成姓名 语文 数学 英语 张三 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' exec(@sql)drop table test
表:tab id1 name id2 100 名称1 200 200 名称2select A.id1,A.name,(select B.name from tab B where B.id1=A.id2) from tab A where A.id1 not in (select id2 from tab)
select a.id1,a.name,b.name from tab a,tab b where a.id2=b.id1
eg1:
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)想变成姓名 语文 数学 英语
张三 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'
exec(@sql)drop table test
id1 name id2
100 名称1 200
200 名称2select A.id1,A.name,(select B.name from tab B where B.id1=A.id2)
from tab A
where A.id1 not in (select id2 from tab)
from tab a,tab b where a.id2=b.id1