如何用SQL语句将表记录的内容筛选后成为一新表的字段
请问各位大侠，如通过SQL语句将以下表一筛选得到表二，如果用SP，那又该怎么写? 多谢！
表一，
产品编号物性编号物性值
1011011.05
10120150
10120685
101207220
1012090.5
10130383
1013060.17
10140122
1033071.2
1032042300
1031011.2
10320160
103206100
103207不断裂
1032090.52-0.58
103302148
1061011.19~1.2
106303140~180
1063096×10
10660192
1066021.49
10640120
106405>10
1061020.3
106501硫酸60%
106502NAOH50%
106506溶解
1211011.41
12120165
1212102700
121206145
121207不断裂
1212090.32
121301165
121303110
1213060.31
表二，
产品编号物性编号101物性编号201物性编号206物性编号207物性编号209
1011.05 50 85 220 0.5
1031.2 60 100 不断裂 0.52~0.58
1061.19~1.2 - - - -
1211.41 65 145 不断裂 0.32
[解决办法]
又是行列转换，LZ搜一下吧，坛子里面好多。
[解决办法]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([产品编号] int,[物性编号] int,[物性值] varchar(9))
insert [tb]
select 101,101,'1.05' union all
select 101,201,'50' union all
select 101,206,'85' union all
select 101,207,'220' union all
select 101,209,'0.5' union all
select 101,303,'83' union all
select 101,306,'0.17' union all
select 101,401,'22' union all
select 103,307,'1.2' union all
select 103,204,'2300' union all
select 103,101,'1.2' union all
select 103,201,'60' union all
select 103,206,'100' union all
select 103,207,'不断裂' union all
select 103,209,'0.52-0.58' union all
select 103,302,'148' union all
select 106,101,'1.19~1.2' union all
select 106,303,'140~180' union all
select 106,309,'6×10' union all
select 106,601,'92' union all
select 106,602,'1.49' union all
select 106,401,'20' union all
select 106,405,'>10' union all
select 106,102,'0.3' union all
select 106,501,'硫酸60%' union all
select 106,502,'NAOH50%' union all
select 106,506,'溶解' union all
select 121,101,'1.41' union all
select 121,201,'65' union all
select 121,210,'2700' union all
select 121,206,'145' union all
select 121,207,'不断裂' union all
select 121,209,'0.32' union all
select 121,301,'165' union all
select 121,303,'110' union all
select 121,306,'0.31'
go
create proc sp_test
as
select
[产品编号],
[物性编号101]=max(case when 物性编号='101' then 物性值 else '-' end),
[物性编号201]=max(case when 物性编号='201' then 物性值 else '-' end),
[物性编号206]=max(case when 物性编号='206' then 物性值 else '-' end),
[物性编号207]=max(case when 物性编号='207' then 物性值 else '-' end),
[物性编号209]=max(case when 物性编号='209' then 物性值 else '-' end)
from
[tb]
group by
[产品编号]
go
exec sp_test
--测试结果:
/*
产品编号 物性编号101 物性编号201 物性编号206 物性编号207 物性编号209
----------- --------- --------- --------- --------- ---------
101 1.05 50 85 220 0.5
103 1.2 60 100 不断裂 0.52-0.58
106 1.19~1.2 - - - -
121 1.41 65 145 不断裂 0.32
(4 行受影响)
*/
