SQLTeam.com | Weblogs | Forums

Why Parts Mass Value Changed From table temppc to table tmpParts when separate by comma?

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

Hi

Your issue is here ..

see what's happening to mass
after you do CAST(t.Mass AS VARCHAR(3500)

image

One Idea
is to make Mass a varchar filed
then enter the data like this '33.73757'

drop table #TempPc

create table #TempPc
(
PartNumber nvarchar(50),
Substance  nvarchar(100),
Mass  varchar(100),
)
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')

Now the answer comes