When group value based on feature and part some times come left and some times come right why and how to solve issue

asked 2020-08-24T07:48:02.313+00:00
ahmed salah 3,126 Reputation points

I work on SQL server 2012 my issue here is when I use only one part then value become
32BIT 1
when may be 10 part then value become 132BIT
meaning cell have two value 32 and 1 some times 1 go to left and some times go to right
I need to know why one go to left and some times go to right
this statement do that

SELECT DISTINCT
[InputID],PART_ID,[Vendor ID],Manufacturer,[Digi-Key Part No.] ,[Mfr Part No.],[Description],Category,Family ,Obsolete ,[Non-Stock]
, [Part_Status],partNumber,CompanyName,DKFeatureName2,[Variant Number],IsUnit,
SUBSTRING((

SELECT isnull(StarFormat,'') + Value+ isnull(endFormat,'')
FROM #FinalTable
WHERE ([InputID] = Results.[InputID] and DkFeatureId = Results.DkFeatureId)
FOR XML PATH(''),TYPE).value('.', 'NVARCHAR(MAX)')

,1,5000) AS Value
FROM #FinalTable Results

value it must be 1 and 32BIT SO why some times come right and some times come left
this is my question

to more clear example :

Part_ID DkFeature Value

12223269 CORE_SIZE 1
12223269 CORE_SIZE 32BIT

when apply select above with sub string
some times will be 32BIT 1
and if it multiple part may be 150 part
then it become 132BIT
why number 1 come on right and some times come on left

{count} votes

4 answers

Sort by: Most helpful
  1. answered 2020-08-24T07:59:27.773+00:00
    Olaf Helper 25,551 Reputation points

    why number 1 come on right and some times come on left

    To be true, I don't understand a single word of your post and your SQL code is not really readable.

    Please post table design as DDL, some sample data as DML statement and the expected result.

    Olaf

    No comments

  2. answered 2020-08-25T07:12:43.197+00:00
    Raja Sekhar S 1 Reputation point

    Hi,

    I assume you are referring to the order of the Value feature. You have to specify the Order by Clause in order to get the same order every time.

    consider the below example

    Create Table #TableName 
    (
        Sno Int, 
        Company Nvarchar(40), 
        Location Nvarchar(100)
    )
    
    Insert into #TableName 
    Values(1,'A','California'),(2,'A','Florida'),(3,'A','Alabama')
    
    Select (Select ',' + a.Location From #TableName a for Xml Path('') ,TYPE).value('.','VARCHAR(Max)') as CSV
    
    Select (Select ',' + a.Location From #TableName a Order by ','+ a.Location for Xml Path('') ,TYPE).value('.','VARCHAR(Max)') as CSV
    
    Drop Table #TableName 
    Go
    

    The output for the first Query will be

    CSV
    ,California,Florida,Alabama
    

    The Output of the Second query will be

    CSV
    ,Alabama,California,Florida
    
    No comments

  3. answered 2020-08-31T09:37:25.58+00:00
    Stefan Hoffmann 616 Reputation points

    Cause building a string using FOR XML works a little bit different. It concats the returned rows into one value. Thus you need to add a row terminator.
    When it is caused by the order of the added pre- and suffix, then you need to add spaces for the affixes.

    Test this for your [Value]:

    SELECT [InputID] ,
           STUFF((   SELECT ', ' + ISNULL(I.StarFormat + ' ', '') + I.[Value] + ISNULL(' ' + I.endFormat, '')
                     FROM   #FinalTable I
                     WHERE  I.InputID = O.InputID
                            AND I.DkFeatureId = O.DkFeatureId
                     FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,
                 1 ,
                 2 ,
                 '') AS [Value]
    FROM   #FinalTable O;
    

    btw, value is a reserved word and should not be used as column name. Furthermore it is a pretty meaningless name. Especially as column names should transport information about the attribute.

    Also use table alias names in a consistent manner to avoid troubles and make reading your statements easier.

    No comments

  4. answered 2020-08-31T19:42:34.197+00:00
    Tom Phillips 17,511 Reputation points

    To be clear.

    Your results are that way because you did not use ORDER BY.

    SELECT DISTINCT
    [InputID],PART_ID,[Vendor ID],Manufacturer,[Digi-Key Part No.] ,[Mfr Part No.],[Description],Category,Family ,Obsolete ,[Non-Stock]
    , [Part_Status],partNumber,CompanyName,DKFeatureName2,[Variant Number],IsUnit,
    SUBSTRING((
    
       SELECT  isnull(StarFormat,'') + Value+ isnull(endFormat,'')
      FROM #FinalTable 
      WHERE ([InputID] = Results.[InputID] and DkFeatureId = Results.DkFeatureId) 
      ORDER BY [Value]
      FOR XML PATH(''),TYPE).value('.', 'NVARCHAR(MAX)')
    ,1,5000) AS Value
    FROM #FinalTable Results
    
    No comments