数据库SQL语句 表格式转换

来源：百度知道 编辑：UC知道 时间：2024/06/01 16:25:48
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语句啊 多谢咯 急求！！

declare @b table(barcode varchar(10), size varchar(5), quantity int, requantity int)
declare @a table(barcode varchar(10), s1 int, s2 int, s3 int,s4 int,s5 int)
insert into @a
select '1009823',0,0,1,0,2 union
select '1009824',3,0,1,0,3 union
select '1009825',-1,2,0,3,0

select * from @a

select * from (
select barcode, 's1' as size,case when s1>0 then s1 else 0 end quantity, case when s1<0 then s1 else 0 end requantity from @a union
select barcode, 's2' as size,case when s2>0 then s2 else 0 end quantity, case when s2<0 then s2 else 0 end requantity from @a union
select barcode, 's3' as size,case when s3>0 then s3 else 0 end quantity, case when s3<0 then s3 else 0 end requantity from @a union
select barcode, 's4' as size,case when s4>0 then s4 else 0 end quantity, case when s4<0 then s4 else 0 end requantity from @a union
select barcode