sql问题，有点烦

Question

如下表格中数据：
id,name,count1,count2,count3,count4
1,wang1,0,1,2,1
2,wang2,1,1,2,1
3,wang3,0,0,0,1
4,wang4,1,0,0,1
...
现在想检索数据插入一张新表，新表中的主要字段如下
id,name,num,count在原始表中，如果count(1或2,3,4)不为0的插入新表，并且进行自动编号写入num字段，并且取其值写入新表的count字段。如前面数据第一、二行插入新表后则是
1,wang1,01,1
1,wang1,02,2
1,wang1,03,1
2,wang2,01,1
2,wang2,02,1
2,wang2,03,2
2,wang2,04,1
...有不明白题目要求的，欢迎跟帖咨询。测试数据表如下：
CREATE TABLE [dbo].[tmpTestTable] (
[id] [bigint] NULL ,
[name] [varchar] (20)  NULL,
        [countvalue1] [int] null,
[countvalue2] [int] null,
[countvalue3] [int] null,
[countvalue4] [int] null
) ON [PRIMARY]
GOinsert into tmpTestTable
select 1,'wang1',0,1,2,1 
union all
select 2,'wang2',1,1,2,1 
union all
select 3,'wang3',0,0,0,1 
union all
select 4,'wang4',1,0,0,1 select * from tmpTestTable
--drop table tmpTestTable谢谢各位了！

Answer 1

create table new(
[id] [bigint] NULL ,
[name] [varchar] (20)  NULL,
[num] [int] null,
        [countvalue] [int] null)insert into new
select id, name, 1, countvalue1 from tmptesttable where countvalue1 <> 0insert into new
select id, name, isnull((select max(num) + 1 from new where id = a.id 
and name = a.name), 1) ---如果id是对应name的，这句不需要
, countvalue2
from tmptesttable a where countvalue2 <> 0insert into new
select id, name, isnull((select max(num) + 1 from new where id = a.id 
and name = a.name), 1) ---如果id是对应name的，这句不需要
, countvalue3
from tmptesttable a where countvalue3 <> 0insert into new
select id, name, isnull((select max(num) + 1 from new where id = a.id 
and name = a.name), 1) ---如果id是对应name的，这句不需要
, countvalue4
from tmptesttable a where countvalue4 <> 0select * from new order by id, num

Answer 2

循环访问名个字段，可以用动态语句实现，并结合syscolumns

Answer 3

select identity(int,1,1) as id1,* into tmp from(
select id,name,countvalue1 as countvalue from tmpTestTable where countvalue1<>0
union all
select id,name,countvalue2 as countvalue from tmpTestTable where countvalue2<>0
union all
select id,name,countvalue3 as countvalue from tmpTestTable where countvalue3<>0
union all
select id,name,countvalue4 as countvalue from tmpTestTable where countvalue4<>0
) a order by name 
insert into newTable select id,name,num=(select right(100+count(*),2) from tmp where a.id=id and a.id1>=id1) ,countvalue from tmp a

Answer 4

CREATE TABLE [dbo].[tmpTestTable] (
[id] [bigint] NULL ,
[name] [varchar] (20)  NULL,
        [countvalue1] [int] null,
[countvalue2] [int] null,
[countvalue3] [int] null,
[countvalue4] [int] null
) ON [PRIMARY]
GOinsert into tmpTestTable
select 1,'wang1',0,1,2,1 
union all
select 2,'wang2',1,1,2,1 
union all
select 3,'wang3',0,0,0,1 
union all
select 4,'wang4',1,0,0,1 declare @t  table(id int,name varchar(10),bh int,countvalue int)
declare @t1 table( i int  identity(1,1),id int,name varchar(10),bh int,countvalue int)insert into @t select id,name,1,countvalue1 from tmpTestTable where countvalue1 <>0
insert into @t select id,name,2,countvalue2 from tmpTestTable where countvalue2 <>0
insert into @t select id,name,3,countvalue3 from tmpTestTable where countvalue3 <>0
insert into @t select id,name,4,countvalue4 from tmpTestTable where countvalue4 <>0insert @t1 select * from (select top 100 percent * from @t order by id,name,bh)aselect id,name,bh=right(cast(101+i-(select min(i) from @t1 where id=t.id) as varchar(5)),2) ,countvalue from @t1 tdrop table tmpTestTable
（所影响的行数为 10 行）id          name       bh   countvalue  
----------- ---------- ---- ----------- 
1           wang1      01   1
1           wang1      02   2
1           wang1      03   1
2           wang2      01   1
2           wang2      02   1
2           wang2      03   2
2           wang2      04   1
3           wang3      01   1
4           wang4      01   1
4           wang4      02   1（所影响的行数为 10 行）

Answer 5

用临时表
select * into #t from 
(
select id,name,'01' as num,countvalue1 as [count] from tmptesttable where countvalue1 > 0 
union all
select id,name,'02' as num,countvalue2 as [count] from tmptesttable where countvalue2 > 0
union all
select id,name,'03' as num,countvalue3 as [count] from tmptesttable where countvalue3 > 0
union all
select id,name,'04' as num,countvalue4 as [count] from tmptesttable where countvalue4 > 0
)t
select id,name,'0'+(select cast(count(1) as char)  from #t where id = t1.id and name = t1.name and num <= t1.num)as num,count
from #t t1
order by id,name,num