Click here to Skip to main content
14,735,364 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more: , +
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 value 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


But I get wrong result on mass because it display data two parts same mass separated by comma on two parts .

But in the table #TempPC two parts are 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


What I have tried:

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
Posted
Updated 9-Nov-20 0:44am
v2

1 solution

In table #TempPc, you define Mass as a float. A float typically has a range of about 10-38 to 1038, and a precision of about 6 digits. This means that a value such as 580.28613 will be rounded to 508.286.

If you use the double type, your value will have a precision of about 16 digits. This, however, does not guarantee that a decimal value will be recorded accurately. A float or double typically use binary arithmetic, so decimal fractions are certain to be rounded. This often does not make a difference for scientific or engineering calculations, but can be crucial in e.g. financial calculations.

If exact arithmetic is required, use the numeric or the decimal types.
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900