Question

请教一个sql语句

表order
ordersid product num
1 a 1
1 b 2
2 b 3
2 c 1
3 c 2
3 a 3

得到的结果如下
ordersid product_a product_b product_c
1 1 2 0
2 0 3 1
3 2 0 3

order表中的product不一定是固定的,有多有少
是乎要用到转置,请牛人帮一下忙写一下通用SQL语句
如果只能用存储过程,请写的详细一点,
我分只有100分,被采纳的,全给了.谢谢!
文本表有点乱,上传一张图片看的清楚点.

Answer 1

分组之后，使用case when 即可

select ordersid,
sum(case when product=a then num end) product_a,
sum(case when product=b then num end) product_b,
sum(case when product=c then num end) product_c
from order
group by ordersid

Answer 2

declare @sql nvarchar(4000)

set @sql='select ordersid'

select @sql=@sql+',sum(case product when '''+product+''' then num else 0 end) as product_'+product from [order] group by product

select @sql=@sql+' from [order] group by ordersid'

exec sp_executesql @sql

Answer 3

小弟看懂1楼的做的东西
只会这个
我是在SQLSERVER2008 下做的

select distinct r.ordersid ,
(select SUM(a.num) from [order] a where a.product='a' and a.ordersid=r.ordersid ) as procduct_a,
(select SUM(b.num) from [order] b where b.product='b' and b.ordersid=r.ordersid ) as procduct_b,
(select SUM(c.num) from [order] c where c.product='c' and c.ordersid=r.ordersid ) as procduct_c
from [order] r

Answer 4

select ordersid,
sum(case product when a then num) product_a,
sum(case product when b then num) product_b,
sum(case product when c then num) product_c
from order
group by ordersid

Answer 5

除了Alex以外,其他应该都错,因为产品的种类是变数