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

3,216 Reputation points
2020-11-07T18:16:37.52+00:00

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
``````
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,664 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,624 questions

1. 9,236 Reputation points
2020-11-07T19:12:19.987+00:00

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

2. 116.6K Reputation points
2020-11-07T19:21:15.763+00:00

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

3. 14,581 Reputation points
2020-11-09T03:23:25.647+00:00

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.