以下是示例数据：
IdProduit Localisation Qte_EnMain 4266864286880063006 E2-R40-B-T 13.00000 4266864286880063006 E2-R45-B-T 81.00000 4266864286880063007 E2-R45-C-T 17.00000 4266864286880063008 E2-R37-B-T 8.00000
这就是我想要的
IdProduit AllLocalisation 4266864286880063006 E2-R40-B-T (13),E2-R45-B-T (81) 4266864286880063007 E2-R45-C-T (17) 4266864286880063008 E2-R37-B-T (8)
我在论坛上看了GROUP_CONCAT的所有例子,我尝试了几个测试.
我真的不懂STUFF().
这是我想做的事情：
SELECT
a.IdProduit,GROUP_CONCAT(
CONCAT(b.Localisation,' (',CAST(ROUND(a.Qte_EnMain,0) AS NUMERIC(36,0)),')')
) AS AllLocation
FROM
ogasys.INV_InventENTLoc a
LEFT JOIN ogasys.INV_LocName b ON a.IdLoc = b.IdLoc
GROUP BY a.IdProduit,b.Localisation,a.Qte_EnMain
现在因为GROUP_CONCAT不能和MSsql一起工作,这是我在这个论坛上用所有例子创建的查询.
SELECT
DISTINCT
a1.IdProduit,STUFF((SELECT DISTINCT '' + b2.Localisation
FROM
ogasys.INV_InventENTLoc a2
LEFT JOIN ogasys.INV_LocName b2 ON a2.IdLoc = b2.IdLoc
WHERE a2.IdLoc = a1.IdLoc
FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)'),1,'') data
FROM
ogasys.INV_InventENTLoc a1
LEFT JOIN ogasys.INV_LocName b1 ON a1.IdLoc = b1.IdLoc
ORDER BY a1.IdProduit
编辑：
这是我的情况的解决方案：
SELECT
a.IdProduit,STUFF(
(SELECT ',' + b2.Localisation + ' (' + CAST(CAST(ROUND(a2.Qte_EnMain,0)) AS VARCHAR(32)) + ')'
FROM ogasys.INV_InventENTLoc a2
LEFT JOIN ogasys.INV_LocName b2 ON a2.IdLoc = b2.IdLoc
WHERE a.IdProduit = a2.IdProduit
FOR XML PATH ('')),'') AS AllLocalisation
FROM
ogasys.INV_InventENTLoc a
LEFT JOIN ogasys.INV_LocName b ON a.IdLoc = b.IdLoc
GROUP BY a.IdProduit
解决方法
使用STUFF
declare @table table (IdProduit varchar(100),Localisation varchar(50),Qte_EnMain float)
insert into @table
values
('4266864286880063006','E2-R40-B-T',13.00000),('4266864286880063006','E2-R45-B-T',81.00000),('4266864286880063007','E2-R45-C-T',17.00000),('4266864286880063008','E2-R37-B-T',8.00000)
select IdProduit,STUFF (
(SELECT
',' + localisation + concat(' (',cast(qte_enMain as varchar(4)),') ')
FROM @table t2
where t2.IdProduit = t1.IdProduit
FOR XML PATH('')),''
)
from @table t1
group by
IdProduit
