生成新表问题，请高手来看一看（请大力，和尚，蚂蚁，j老板，不兼容等兄弟都来帮忙看看）

[yoki]

create table a(id int,t1 int,t2 int,t3 int,t4 int)
insert into a(id,t1) values(1,51)
insert into a(id,t2) values(2,31)
insert into a(id,t1) values(3,61)
insert into a(id,t1,t2) values(4,51,41)
insert into a values(5,11,71,81,31)

表a如下：
id t1 t2 t3 t4
1 51 NULL NULL NULL
2 NULL 31 NULL NULL
3 61 NULL NULL NULL
4 51 41 NULL NULL
5 11 71 81 31
如何得到表b:(即获得有数据的t的序号)
id t_code
1 1
2 2
3 1
4 1
4 2
5 1
5 2
5 3
5 4

[hjhing]

select id,1 from a where t1 is not null
union
select id,2 from a where t2 is not null
union
select id,3 from a where t3 is not null
union
select id,4 from a where t4 is not null

[CrazyFor]

select a.id,1
from a left join A a1 on a.id=a1.id and a1.t1 is not null
union
select a.id,2
from a left join A a2 on a.id=a2.id and a2.t2 is not null
union
select a.id,3
from a left join A a3 on a.id=a3.id and a3.t3 is not null
union
select a.id,4
from a left join A a4 on a.id=a4.id and a4.t4 is not null

方法有点笨。：）

[hjhing]

---if need sorted
select * from
(
select id,1 as t_code from a where t1 is not null
union
select id,2 as t_code from a where t2 is not null
union
select id,3 as t_code from a where t3 is not null
union
select id,4 as t_code from a where t4 is not null
) as T
order by [id]

[yoki]

我是本来是这样做的，不过好像效率比较低，请问还有没有更好的方法？
select d.* into #t
from
(
select id,case when t1 is not null then 1 end code from a
union
select id,case when t2 is not null then 2 end code from a
union
select id,case when t3 is not null then 3 end code from a
union
select id,case when t4 is not null then 4 end code from a
)d
where d.code is not null

[CrazyFor]

建序数表：
select top 8000 identity(int,1,1) as N into nums from
(select top 100 id=1 from sysobjects) as a,
(select top 100 id=1 from sysobjects) as b,
(select top 100 id=1 from sysobjects) as c

Nums为序数表，N为表中字段名：

TRY：
select a.id,c.N from a
left join a b on a.id=b.id
left join NUMs c
on (b.t1 is not null and c.N=1)
or (b.t2 is not null and c.N=2)
or (b.t3 is not null and c.N=3)
or (b.t4 is not null and c.N=4)

[happydreamer]

select * from
(
select id ,1 as tc_code from a where t1 is not null
union all
select id ,2 as tc_code from a where t2 is not null
union all
select id ,3 as tc_code from a where t3 is not null
union all
select id, 4 as tc_code from a where t4 is not null
)a order by a.id,tc_code

还有其他方法么，期待

[CrazyFor]

测试了一下建序数表的方法，没有问题。：）

[yoki]

好,谢谢大家

[yoki]

还有更好的方法没有？期待。。。（马上接贴）

[pengdali]

在没有辅助表的情况下union all是最好的解决方法！