请各位高手帮忙
我有这样的表:
------------
ID 1m 2m 3m
004 500 300 200
005 200 100 30
转换后插入到新表的数据:
------------
id mid sorft je
1 004 1 500
2 004 2 300
3 004 3 200
4 005 1 200
5 005 2 100
6 005 3 30name
我有这样的表:
------------
ID 1m 2m 3m
004 500 300 200
005 200 100 30
转换后插入到新表的数据:
------------
id mid sorft je
1 004 1 500
2 004 2 300
3 004 3 200
4 005 1 200
5 005 2 100
6 005 3 30name
select id as mid,1 as sorft,1m as je from 表名 union all
select id,2,2m from 表名 union all
select id,3,3m from 表名)a
--sqlserver2005以上版本适用
select * into #t from
(
select '004' id,500 [1m],300 [2m],200 [3m]
union
select '005',200,100,30
)a
select * from #tselect row_number() over(order by id,sorft) as id,id as mid,replace(sorft,'m','')sorft,je
from #t unpivot(je for sorft in ([1m],[2m],[3m]))a/*
id 1m 2m 3m
---- ----------- ----------- -----------
004 500 300 200
005 200 100 30id mid sorft je
---- ---- ------ -----
1 004 1 500
2 004 2 300
3 004 3 200
4 005 1 200
5 005 2 100
6 005 3 30
*/
http://bbs.csdn.net/topics/240002706
as(
select '004',500,300,200 union all
select '005',200,100,30
)select id=row_number()over(order by getdate()),* from
(
select mid,sorft=row_number()over(partition by mid order by je desc),je from(
select id mid,[1m] je from tb union all
select id mid,[2m] je from tb union all
select id mid,[3m] je from tb
)t
)tt