Why Parts Mass Value changed From table temppc to table tmpParts when separated by comma and How to solve?

Question

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

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 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

Answer 1

Use FORMAT instead of CAST for the float values. This will provide more control over the formatting of the float value:

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 ',' + FORMAT(t.Mass, '0.#####')
                             FROM #TempPC t
                             WHERE t.PartNumber = p.PartNumber
                             ORDER BY t.Mass
                             FOR XML PATH('')), 1, 1, '') AS NVARCHAR(3500))
FROM #tmpParts p;

Answer 2

According to previous unfinished topic (https://learn.microsoft.com/en-us/answers/questions/153386/how-to-remove-last-two-number-ending-after-float-p.html), you can try CONVERT here too:

. . .
p.strMass = CAST (STUFF((SELECT ',' + CONVERT(varchar(3500), t.Mass, 128)
. . .

Answer 3

Hi @ahmed salah ，

I tested the solutions of other experts, but it doesn't seem to work. I tried to use the string_agg function, but the result will still be truncated.

Truncating and rounding results
When converting character or binary expressions (binary, char, nchar, nvarchar, varbinary, or varchar) to an expression of a different data type, the conversion operation could truncate the output data, only partially display the output data, or return an error. These cases will occur if the result is too short to display. Conversions to binary, char, nchar, nvarchar, varbinary, or varchar are truncated, except for the conversions shown in the following table.

For more details, please refer to：CAST and CONVERT (Transact-SQL)(Truncating and rounding results)

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues October--https://learn.microsoft.com/en-us/answers/questions/142427/users-always-get-connection-timeout-problem-when-u.html