SQL数据透视表到列

Question

我有一个查询按代理机构“Vente”和“Recouv”返回两种类型。我希望我的请求显示一行与“委员会”栏的总和。

这里是一个画面，显示我有什么什么，我想：

select codeAgence,CASE WHEN (TypeOperation =0) THEN 'Vente' ELSE 'Recouvrement' END as 'Type', 
count(*) 'Nb',SUM(PxVenteHT) 'C.A',SUM(acompte) 'Montant',SUM(MontantCommission) 'Commission' from Commission_Archive 
    group by CodeAgence,TypeOperation 
    order by CodeAgence 

Answer 1

我想这可能会做你想要什么，但你的问题缺乏适当的示例源数据I避风港没有对它进行测试。

SELECT 
    codeAgence, 
    COUNT(CASE WHEN (TypeOperation = 0) THEN 'Vente' ELSE NULL END) 'Nb Vente', 
    SUM(CASE WHEN (TypeOperation = 0) THEN PxVenteHT ELSE 0 END) 'C.A', 
    COUNT(CASE WHEN (TypeOperation <> 0) THEN 'Recouve' ELSE NULL END) 'Nb Recouve', 
    SUM(CASE WHEN (TypeOperation <> 0) THEN acompte ELSE 0 END) 'Montant Recouve', 
    SUM(MontantCommission) 'Commission' 
FROM Commission_Archive 
GROUP BY CodeAgence 
ORDER BY CodeAgence 

关键是要有条件的聚合技术应用到所有应该对Nb/Recouve拆分和改变分组汇总列。