现有一个数据量很大的表,表结构如下:
表名:a
结构:id name para1 para2 para3
1 n1 a1 b1 c1
2 n2 a2 b2 c2 如何将表a的一行拆分成多行,创建一个新表b,新表b中还要添加一个新字段,如下
表名:b
结构:id name para paraDesc
1 n1 a1 pa1
2 n1 b1 pb1
3 n1 c1 pc1
4 n2 a2 pa2
5 n2 b2 pb2
6 n2 c2 pc2
现有一项目要这样做,请高手指点,不胜感激!急
表名:a
结构:id name para1 para2 para3
1 n1 a1 b1 c1
2 n2 a2 b2 c2 如何将表a的一行拆分成多行,创建一个新表b,新表b中还要添加一个新字段,如下
表名:b
结构:id name para paraDesc
1 n1 a1 pa1
2 n1 b1 pb1
3 n1 c1 pc1
4 n2 a2 pa2
5 n2 b2 pb2
6 n2 c2 pc2
现有一项目要这样做,请高手指点,不胜感激!急
create table a (id int, name varchar(10), para1 varchar(10), para2 varchar(10), para3 varchar(10))
insert into a
select 1, 'n1', 'a1', 'b1', 'c1' union all
select 2, 'n2', 'a2', 'b2', 'c2' select * from adrop table b
select name,para1 as para,'p'+para1 as paraDesc into b from a
union all
select name,para2, 'p'+para2 as paraDesc from a
union all
select name,para3, 'p'+para3 as paraDesc from a
order by nameselect * from balter table b add id int identity(1,1)goselect id,name,para,paraDesc from b/*
id name para paraDesc
--------------------------------------------------
n1 a1 pa1 1
n1 b1 pb1 2
n1 c1 pc1 3
n2 c2 pc2 4
n2 b2 pb2 5
n2 a2 pa2 6
*/
create table a (id int, name varchar(10), para1 varchar(10), para2 varchar(10), para3 varchar(10))
insert into a
select 1, 'n1', 'a1', 'b1', 'c1' union all
select 2, 'n2', 'a2', 'b2', 'c2' select * from adrop table b
select name,para1 as para,'p'+para1 as paraDesc into b from a
union all
select name,para2, 'p'+para2 as paraDesc from a
union all
select name,para3, 'p'+para3 as paraDesc from a
order by nameselect * from balter table b add id int identity(1,1)goselect id,name,para,paraDesc from b/*
id name para paraDesc
--------------------------------------------------1 n1 a1 pa1
2 n1 b1 pb1
3 n1 c1 pc1
4 n2 c2 pc2
5 n2 b2 pb2
6 n2 a2 pa2*/
insert into a
select 1, 'n1', 'a1', 'b1', 'c1' union all
select 2, 'n2', 'a2', 'b2', 'c2'select name,para1 as para,'p'+para1 as paraDesc into # from a
union all
select name,para2 as para,'p'+para2 as paraDesc from a
union all
select name,para3 as para,'p'+para3 as paraDesc from a
order by nameselect identity(int,1,1) as id,* into #1 from #select * from #1drop table #,#1,a/*
id name para paraDesc
----------- ---------- ---------- -----------
1 n1 a1 pa1
2 n1 b1 pb1
3 n1 c1 pc1
4 n2 c2 pc2
5 n2 b2 pb2
6 n2 a2 pa2
*/
select name,para1 as para,'p'+para1 as paraDesc from a
union all
select name,para2 as para,'p'+para2 from a
union all
select name,para3 as para,'p'+para3 from a
order by name,paraDesc