I work on SQL Server 2012. When using Stuff to collect data separated by comma, I get strange results. Mass number separated by comma in table #tmpParts
Not exactly what exist in the original table #TempPC .
To summarize my issue mass for part 5223986-2 for aluminium
- is 580.28613 in table
#TempPC - is 580.286 in table
#tmpParts
Why values changed for mass and how to separated by comma exactly as input on mass values #Temppc .
Also Another Values Changed
create table #TempPc
(
PartNumber nvarchar(50),
Substance nvarchar(100),
Mass Float,
)
insert into #TempPc(PartNumber,Substance,Mass)
values
('5223986-2','Copper',33.73757),
('5223986-2','Zinc',12824.526),
('5223986-2','Aluminum',580.28613),
('5223986-5','Copper', 33.73756),
('5223986-5','Zinc', 12824.52563),
('5223986-5','Aluminum', 580.28612)
CREATE TABLE #tmpParts
(
id INT IDENTITY ,
PartNumber nvarchar(50),
cnt INT ,
strSubstances NVARCHAR(MAX) ,
strMass NVARCHAR(MAX)
)
INSERT INTO #tmpParts
( PartNumber ,
cnt
)
SELECT t.PartNumber ,
COUNT(t.Substance)
FROM #TempPC t
GROUP BY t.PartNumber
UPDATE p
SET p.strSubstances = CAST (STUFF(( SELECT ','
+ CAST(t.Substance AS VARCHAR(3500))
FROM #TempPC t
WHERE t.PartNumber = p.PartNumber
ORDER BY t.Substance
FOR
XML PATH('')
), 1, 1, '') AS NVARCHAR(3500)) ,
p.strMass = CAST (STUFF(( SELECT ','
+ CAST(t.Mass AS VARCHAR(3500))
FROM #TempPC t
WHERE t.PartNumber = p.PartNumber
ORDER BY t.Mass
FOR
XML PATH('')
), 1, 1, '') AS NVARCHAR(3500))
FROM #tmpParts p
But I get wrong Result on Mass Because it display data two parts Same Mass separated By Comma ON Two Parts . but on Table #TempPC Two parts Different on Mass Value.
id PartNumber cnt strSubstances strMass
1 5223986-2 3 Aluminum,Copper,Zinc 33.7376,580.286,12824.5
2 5223986-5 3 Aluminum,Copper,Zinc 33.7376,580.286,12824.5
Final Result I need Same Numbers on #TempPC with Comma Separated on Table #tmpParts ON strMass :
id PartNumber cnt strSubstances strMass
1 5223986-2 3 Aluminum,Copper,Zinc 33.73757,580.28613,12824.526
2 5223986-5 3 Aluminum,Copper,Zinc 33.73756,580.28612,12824.52563
