select a.kind , one ,two,three from (select kind, value as one from table where item = 'one') a, (select kind, value as two from table where item = 'two') b, (select kind, value as three from table where item = 'three')c where a.kind = b.kind and a.kind = c.kind
to : firetoucher(风焱) 这样不行啊,表中item 的值也就是 one two three ..有64个 整个表中数据大约有5000左右,这样数据一大效率就低了
declare @sql varchar(8000) set @sql = 'select kind' select @sql = @sql + ',max(case item when '''+item+''' then value end) ['+item+']' from (select distinct item from 有一表) as a select @sql = @sql+' from 有一表 group by kind'exec(@sql) go
declare @sql varchar(8000) set @sql = 'select kind' select @sql = @sql + ',max(case item when '''+item+''' then value end) ['+item+']' from (select distinct item from 有一表) as a select @sql = @sql+' from first group by kind'exec(@sql) go 这个是动态的,用max就可以对varchar类型了。
from
(select kind, value as one from table where item = 'one') a,
(select kind, value as two from table where item = 'two') b,
(select kind, value as three from table where item = 'three')c
where a.kind = b.kind and a.kind = c.kind
这样不行啊,表中item 的值也就是 one two three ..有64个
整个表中数据大约有5000左右,这样数据一大效率就低了
declare @sql varchar(8000)
set @sql = 'select kind'
select @sql = @sql + ',max(case item when '''+item+''' then value end) ['+item+']'
from (select distinct item from 有一表) as a
select @sql = @sql+' from 有一表 group by kind'exec(@sql)
go
set @sql = 'select kind'
select @sql = @sql + ',max(case item when '''+item+''' then value end) ['+item+']'
from (select distinct item from 有一表) as a
select @sql = @sql+' from first group by kind'exec(@sql)
go
这个是动态的,用max就可以对varchar类型了。
解决了困扰我好久的问题
原来max可以对varchar