数据库SQL 表的转换问题
A表的结构是：
barcode s1 s2 s3 s4 s5
1009823 0 0 1 0 2
1009824 3 0 1 0 3
1009825 -1 2 0 3 0
变换成B表：
barcode size quantity requantity
1009823 s3 1 0
1009823 s4 2 0
1009824 s1 3 0
1009824 s3 1 0
1009824 s5 3 0
1009825 s1 0 -1
1009825 s2 2 0
1009825 s4 3 0
其中barcode是字符型的，S1--S5是int型的，正值记录在quantity中，负值记录在requantity中
怎么编写SQL语句啊 多谢咯 急求！！
[解决办法]
select barcode,'s1' as size,if(quantity>0,quantity,0) as quantity,if(quantity<0,quantity,0) as requantity from a union allselect barcode,'s2' as size,if(quantity>0,quantity,0) as quantity,if(quantity<0,quantity,0) as requantity from a union allselect barcode,'s3' as size,if(quantity>0,quantity,0) as quantity,if(quantity<0,quantity,0) as requantity from a union allselect barcode,'s4' as size,if(quantity>0,quantity,0) as quantity,if(quantity<0,quantity,0) as requantity from a union allselect barcode,'s5' as size,if(quantity>0,quantity,0) as quantity,if(quantity<0,quantity,0) as requantity from a
[解决办法]
select t1.* from (select barcode,'s1' as size,case when s1>0 then s1 else 0 end as quantity, case when s1<0 then s1 else 0 end as requantity from a union allselect barcode,'s2' as size,case when s2>0 then s2 else 0 end as quantity, case when s2<0 then s2 else 0 end as requantity from a union allselect barcode,'s3' as size,case when s3>0 then s3 else 0 end as quantity, case when s3<0 then s3 else 0 end as requantity from a union allselect barcode,'s4' as size,case when s4>0 then s4 else 0 end as quantity, case when s4<0 then s4 else 0 end as requantity from a union allselect barcode,'s5' as size,case when s5>0 then s5 else 0 end as quantity, case when s5<0 then s5 else 0 end as requantity from a ) t1order by t1.barcode
