固定行、列的行列转换......

[tsunekou]

table1:

ID v1 v2 v3
----------------------------
a01 5 1 9
a02 8 5 4
a03 4 10 3
a04 4 2 10

转换为

table2:
id f1 f2 f3 f4 f5 f6 f7 f8 f9 f10
---------------------------------------------
a01 * * *
a02 * * *
a03 * * *
a04 * * *

[lw1a2]

select id,
(case when (v1=1 or v2=1 or v3=1) then '*' else '' end) as f1,
(case when (v1=2 or v2=2 or v3=2) then '*' else '' end) as f2,
(case when (v1=3 or v2=3 or v3=3) then '*' else '' end) as f3,
(case when (v1=4 or v2=4 or v3=4) then '*' else '' end) as f4,
(case when (v1=5 or v2=5 or v3=5) then '*' else '' end) as f5,
(case when (v1=6 or v2=6 or v3=6) then '*' else '' end) as f6,
(case when (v1=7 or v2=7 or v3=7) then '*' else '' end) as f7,
(case when (v1=8 or v2=8 or v3=8) then '*' else '' end) as f8,
(case when (v1=9 or v2=9 or v3=9) then '*' else '' end) as f9,
(case when (v1=10 or v2=10 or v3=10) then '*' else '' end) as f10
from table1

[samfeng_2003]

select ID,[f1]=max(case when col=1 then '*' else '' end),
[f2]=max(case when col=2 then '*' else '' end),
[f3]=max(case when col=3 then '*' else '' end),
[f4]=max(case when col=4 then '*' else '' end),
[f5]=max(case when col=5 then '*' else '' end),
[f6]=max(case when col=6 then '*' else '' end),
[f7]=max(case when col=7 then '*' else '' end),
[f8]=max(case when col=8 then '*' else '' end),
[f9]=max(case when col=9 then '*' else '' end),
[f10]=max(case when col=10 then '*' else '' end) from
(select id,col=v1 from t union all
select id,v2 from t union all
select id,v3 from t) a group by id

[samfeng_2003]

create table t
(ID varchar(3),v1 int,v2 int,v3 int)

insert t
select 'a01',5,1,9 union all
select 'a02',8,5,4 union all
select 'a03',4,10,3 union all
select 'a04',4,2,10

declare @sql varchar(8000),@i int
select @sql=''
select @i=max(col) from
(select col=v1 from t union all
select v2 from t union all
select v3 from t) a

while @i>0
select @sql=',[f'+cast(@i as varchar)+']=max(case when col='+cast(@i as varchar)+' then ''*'' else '''' end)'+@sql,
@i=@i-1
select @sql='select ID'+@sql+' from (select id,col=v1 from t union all
select id,v2 from t union all
select id,v3 from t) a group by id'
exec(@sql)

drop table t

[OracleRoob]

create table #t(id varchar(20), v1 int, v2 int, v3 int)

insert into #t(id,v1,v2,v3) values('a01',5,1,9)
insert into #t(id,v1,v2,v3) values('a02',8,5,10)
insert into #t(id,v1,v2,v3) values('a03',4,10,3)
insert into #t(id,v1,v2,v3) values('a04',4,2,10)

select id,
case when 1 in (v1,v2,v3) then '*' else '' end as f1,
case when 2 in (v1,v2,v3) then '*' else '' end as f2,
case when 3 in (v1,v2,v3) then '*' else '' end as f3,
case when 4 in (v1,v2,v3) then '*' else '' end as f4,
case when 5 in (v1,v2,v3) then '*' else '' end as f5,
case when 6 in (v1,v2,v3) then '*' else '' end as f6,
case when 7 in (v1,v2,v3) then '*' else '' end as f7,
case when 8 in (v1,v2,v3) then '*' else '' end as f8,
case when 9 in (v1,v2,v3) then '*' else '' end as f9,
case when 10 in (v1,v2,v3) then '*' else '' end as f10
from #t

drop table #t

/*
id f1 f2 f3 f4 f5 f6 f7 f8 f9 f10
------------------------------------------------------------------------------------------
a01 * * *
a02 * * *
a03 * * *
a04 * * *


*/