统计同一个客户下单的产品数量

[snlixing]

请大神帮忙，按产品编号和合同号统计同一个客户下单的产品数量

有如下表：


需得到如下表：

[snlixing]

大神们，这个sql怎么写呢

[snlixing]

这个怎么写呢

[qq_25073223]

这是一个行列转换的问题，请参阅这个里面行列转换的知识点 这个里面的例子和你的表结构非常的类似，有疑问及时沟通！

[qq_39174826]

--插入测试数据
select 'CNO-1' as [合同号],'A1' as '产品编号', 100 as [数量] into #Z
union all
select 'CNO-1' as [合同号],'A2' as '产品编号', 100 as [数量]
union all
select 'CNO-1' as [合同号],'A3' as '产品编号', 50 as [数量]
union all
select 'CNO-2' as [合同号],'A3' as '产品编号', 100 as [数量]
union all
select 'CNO-2' as [合同号],'A5' as '产品编号', 50 as [数量]
union all
select 'CNO-3' as [合同号],'A1' as '产品编号', 200 as [数量]
union all
select 'CNO-3' as [合同号],'A2' as '产品编号', 200 as [数量]
union all
select 'CNO-4' as [合同号],'A4' as '产品编号', 150 as [数量]
--插入测试数据完毕

--开始取数据
select * from #Z
pivot(
sum([数量])
for [合同号]
in ([CNO-1],[CNO-2],[CNO-3],[CNO-4])
)p


drop table #Z

结果

[小野马1209]

把楼上的改成动态列显示，因为看你的案例有多个【合同号】

--插入测试数据
select 'CNO-1' as [合同号],'A1' as '产品编号', 100 as [数量] into #Z
union all
select 'CNO-1' as [合同号],'A2' as '产品编号', 100 as [数量]
union all
select 'CNO-1' as [合同号],'A3' as '产品编号', 50 as [数量]
union all
select 'CNO-2' as [合同号],'A3' as '产品编号', 100 as [数量]
union all
select 'CNO-2' as [合同号],'A5' as '产品编号', 50 as [数量]
union all
select 'CNO-3' as [合同号],'A1' as '产品编号', 200 as [数量]
union all
select 'CNO-3' as [合同号],'A2' as '产品编号', 200 as [数量]
union all
select 'CNO-4' as [合同号],'A4' as '产品编号', 150 as [数量]
--插入测试数据完毕

SELECT * FROM #Z

--用SQL语句拼接，这样就可以实现动态列
DECLARE @name VARCHAR(max),@sql VARCHAR(max)
SET @name=stuff((SELECT DISTINCT ',[' + 合同号 + ']'  from #Z for xml PATH('')),1,1,'')

PRINT @name
SET @sql = 'SELECT * FROM #Z PIVOT(SUM([数量]) FOR [合同号] IN(' + @name + '))P'
PRINT @sql
EXEC( @sql)


drop table #Z