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

Question

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

Answer 1

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

Answer 2

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

Answer 3

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.

Answer 4

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