age num bili bili2
--------------------------------------
19-24岁 0 0.0% -100% 怎么变成19-24岁
--------
0
0.0%
-100%请教了.
--------------------------------------
19-24岁 0 0.0% -100% 怎么变成19-24岁
--------
0
0.0%
-100%请教了.
union all
select bili as '19-24岁' from tb
union all
select bili2 as '19-24岁' from tb
select num as '19-24岁' from 表 union all
select bili as '19-24岁' from 表 union all
select bili2 as '19-24岁' from 表
--------------------------------------
19-24岁 0 0.0% -100%
24-30岁 1 20% 100%
那这种改成19-24岁 24-30岁
-------------------------------------
0 1
0.0% 20%
-100% 100%就不行了啊
set @sql = ''
select @sql = @sql +'select num as '''+age+''' from tb'
select @sql = @sql +'union all'
select @sql = @sql +'select bili as '''+age+''' from tb'
select @sql = @sql +'union all'
select @sql = @sql +'select bili2 as '''+age+''' from tb'
exec(@sql)
'select num as '''+age+''' from tb'
的语句好象不成立吧。
但都要用到union all
create table a(age varchar(20), num int, bili varchar(20),bili2 varchar(20))insert a select '19-24岁',0,'0.0%','-100%'
union all select '24-30岁',1,'20%','100%'
union all select '30-50岁',1,'30%','90%'
union all select '50-60岁',1,'80%','99%'
union all select '60-70岁',1,'30%','99%'
declare @age varchar(20),@bili varchar(20),@bili2 varchar(20),@num int
declare @sql varchar(8000),@str varchar(200)
declare @i intset @sql=''
set @i=1
declare cur cursor for select age,num,bili,bili2 from a
open cur
fetch next from cur into @age,@num,@bili,@bili2set @sql='(select 1 id,'''+ltrim(@num)+''' as ['+@age+'] Union All '+
'select 2 ,'''+@bili+''' as ['+@age+'] Union All '+
'select 3 ,'''+@bili2+''' as ['+@age+']) '+char(65)
set @str='['+@age+']'
fetch next from cur into @age,@num,@bili,@bili2
while @@fetch_status=0
begin set @sql=@sql+ case when @i=1 then '' else ' On A.id='+char(65+@i-1)+'.id ' end+' Inner Join '+
'(select 1 id,'''+ltrim(@num)+''' as ['+@age+'] Union All '+
'select 2 ,'''+@bili+''' as ['+@age+'] Union All '+
'select 3 ,'''+@bili2+''' as ['+@age+']) '+char(65+@i)
set @str=@str+',['+@age+']'
fetch next from cur into @age,@num,@bili,@bili2
set @i=@i+1
endclose cur
deallocate cur
set @sql='select '+@str+' from '+@sql+case when @i>0 then ' on A.id='+char(65+@i-1)+'.id' else '' endexec(@sql)--result
/*19-24岁 24-30岁 30-50岁 50-60岁 60-70岁
------ ------ ------ ------ ------
0 1 1 1 1
0.0% 20% 30% 80% 30%
-100% 100% 90% 99% 99%*/