schoolID SchoolName SchoolAddress
0001 schoolA beijing
0002 schoolB shanghai
0003 schoolC xiamen
0004 schoolD fujian ---最好是作好excel再导入表
insert X
select
schoolID,
'SchoolName',--楼主补充
'SchoolAddress'--楼主补充
from
AgenderID genderType
1 male
2 female --直接写入
insert Y
select 'male'
union
select 'female'
ageID age
1 5
2 6
3 7
--
insert Y
select
replace(replace(Name,'boy_age',''),'girl_age','')--取年龄段，数据不多时，楼主直接insert
from
syscolumns
where
ID=object_ID('A') and name<>'schoolID'
group by
replace(replace(Name,'boy_age',''),'girl_age','')
order by colidcountID amount schoolID genderID ageID
1 20 0001 1 1
2 34 0001 1 2
3 44 0001 1 3
4 39 0001 2 1
5 23 0001 2 2
6 25 0001 2 3
7 24 0002 1 1
8 36 0002 1 2
9 43 0002 1 3
10 39 0002 2 1
11 23 0002 2 2
12 22 0002 2 3
create table A(schoolID char(4), boy_age5 int,boy_age6 int, boy_age7 int, girl_age5 int,girl_age6 int, girl_age7 int)insert A select '0001', 20, 34, 44, 39, 23, 25 declare @s nvarchar(4000)
select
@s=isnull(@s+' union all ','')+'select [amount]='+quotename(Name)+',schoolID,[genderID]='+case when left(Name,3)='boy' then '1' else '2' end --这里列名前三个字母不是boy是为2
+',ageID=(select ID from Y where age='+replace(replace(Name,'boy_age',''),'girl_age','')+') from A'
from
syscolumns a
where
ID=object_id('A')exec ('insert R '+@s)生成的语句如下:
insert R
select [amount]=[boy_age5],schoolID,[genderID]=1,ageID=(select ID from Y where age=5) from A union all
select [amount]=[boy_age6],schoolID,[genderID]=1,ageID=(select ID from Y where age=6) from A union all
select [amount]=[boy_age7],schoolID,[genderID]=1,ageID=(select ID from Y where age=7) from A union all
select [amount]=[girl_age5],schoolID,[genderID]=2,ageID=(select ID from Y where age=5) from A union all
select [amount]=[girl_age6],schoolID,[genderID]=2,ageID=(select ID from Y where age=6) from A union all
select [amount]=[girl_age7],schoolID,[genderID]=2,ageID=(select ID from Y where age=7) from A union all
select [amount]=[schoolID],schoolID,[genderID]=2,ageID=(select ID from Y where age=schoolID) from A
select [amount]=[boy_age5],schoolID,[genderID]=1,ageID=(select ID from Y where age=5) from A union all
select [amount]=[boy_age6],schoolID,[genderID]=1,ageID=(select ID from Y where age=6) from A union all
select [amount]=[boy_age7],schoolID,[genderID]=1,ageID=(select ID from Y where age=7) from A union all
select [amount]=[girl_age5],schoolID,[genderID]=2,ageID=(select ID from Y where age=5) from A union all
select [amount]=[girl_age6],schoolID,[genderID]=2,ageID=(select ID from Y where age=6) from A union all
select [amount]=[girl_age7],schoolID,[genderID]=2,ageID=(select ID from Y where age=7) from A union all
select [amount]=[schoolID],schoolID,[genderID]=2,ageID=(select ID from Y where age=schoolID) from A
order by schoolID,genderID,ageID--可加上排序