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

Transact-SQL
,
#1

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

#2

to more clear example :slight_smile:
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

#3

hi

if you can post some sample data .. with both scenarios covered ..

Example = Create Table

#4

Also you can debug it ... Yourself ..
which is what WE do also anyway

Take only the part of SQL where you are doing the apply ..
Take only 2 or 3 records of data ..

And then see the statement .. bit by bit
example
select abc cross apply def
what is in abc what is in def .. whats happening cross apply

#5

two case for issue in image and excel i will attach
this data for #final table

two cases have issue
two cases have issue1857×406 20.8 KB

also file attached with data
http://www.mediafire.com/file/7olxi80zwtpiyab/twocaseshave_issue.xlsx/file

#6

hi

can please post the two case for issue .. as ddl and dml data
like below

Example = Create Table

#7 
drop table #FinalTable
drop table #ff
Create Table #FinalTable
(	
	InputID INT ,
	PartNumber Nvarchar(500),
	CompanyName nvarchar(500),
	PartID int null,
	DkFeatureId int,
	PART_ID NVARCHAR(500),
	ZfeatureId int,
	[Value] nvarchar(4000),
	Separator nvarchar(50),
	SeparatorOrder int,
	FeatureType int,
	DKFeatureName nvarchar(100),
	DisplayOrder int,
	IsUnit int,
	SeparatValue nvarchar(50),
	Starformat  nvarchar(50),
	Endformat  nvarchar(50),
	DkFeatureName2 nvarchar(100),
	StatusId int
)
insert into #FinalTable (InputID,PartNumber,CompanyName,PartID,PART_ID,DkFeatureId,ZfeatureId,StatusId,Separator,SeparatorOrder,Starformat,Endformat,FeatureType,Value,DKFeatureName,DisplayOrder,IsUnit,SeparatValue,DkFeatureName2	
)
values
(124,'PIC32MZ2025DAR176-V/2J','Microchip Technology Incorporated',32106630,'12823269',724541,1500500006,3,'' ,1,NULL,NULL,2044,'32-Bit','Core Size',4,0,NULL,'Core Size (689)'),	
(124,'PIC32MZ2025DAR176-V/2J','Microchip Technology Incorporated',32106630,'12823269',724541,1500500011,3,NULL,2,NULL,NULL,2044,'1','Core Size',4,0,NULL,'Core Size (689)'),	
(128,'PIC32MZ2025DAS176-V/2J','Microchip Technology Incorporated',32106634,'12823241',724541,1500500006,3,'' ,1,NULL,NULL,2044,'32-Bit','Core Size',4,0,NULL,'Core Size (689)'),	
(128,'PIC32MZ2025DAS176-V/2J','Microchip Technology Incorporated',32106634,'12823241',724541,1500500006,3,NULL,2,NULL,NULL,2044,'1','Core Size',4,0,NULL,'Core Size (689)'),
(141,'PIC32MZ2064DAS176T-V/2J','Microchip Technology Incorporated',32106647,'12823374',724541,1500500011,3,NULL,2,NULL,NULL,2044,'1','Core Size',4,0,NULL,'Core Size (689)'),
(141,'PIC32MZ2064DAS176T-V/2J','Microchip Technology Incorporated',32106647,'12823374',724541,1500500011,3,NULL,1,NULL,NULL,2044,'32-Bit','Core Size',4,0,NULL,'Core Size (689)')


--128	PIC32MZ2025DAS176-V/2J	Microchip Technology Incorporated	32106634	7763416	46286	12823241	150	Microchip	150-PIC32MZ2025DAS176-V/2J-ND	PIC32MZ2025DAS176-V/2J	32-BIT CACHE-BASED MCU, GRAPHICS	Integrated Circuits (ICs)	Embedded - Microcontrollers	Active	Non Stocking	Exact	V-Article_1582340902508557	14139	724541	1500500006	3	 	1	NULL	NULL	2044	1423	32-Bit	Core Size	4	32	0	NULL	Core Size (689)	0	NULL	NULL	NULL
select * INTO #FF  from  (
SELECT DISTINCT
  [InputID],PART_ID,partNumber,CompanyName,DKFeatureName2,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
where StatusId=3
)t
select * from #FF
InputID PART_ID partNumber CompanyName DKFeatureName2 IsUnit Value
124 12823269 PIC32MZ2025DAR176-V/2J Microchip Technology Incorporated Core Size (689) 0 32-Bit1
128 12823241 PIC32MZ2025DAS176-V/2J Microchip Technology Incorporated Core Size (689) 0 32-Bit1
141 12823374 PIC32MZ2064DAS176T-V/2J Microchip Technology Incorporated Core Size (689) 0 132-Bit

I need PART_ID 12823374 and input id 141 value to be 32-Bit1
132-Bit is wrong
32-Bit1 is correct
so how to do that please

#8

hi

i will work on it NOW .. please please wait

#9

hi

i got the issue ..

see for partids .. 32-bit is ther first and 1 next

image

for this part id 1 is first and 32-bit is next thats why
image

you can do an ( order by value descending or ascending depends) .. if you want everything to come as 32bit 1

#10

so how to do order by can you help me please

#11

hope this helps .. please try .. i have put the order by

select * INTO #FF  from  (
SELECT DISTINCT
  [InputID],PART_ID,partNumber,CompanyName,DKFeatureName2,IsUnit,
  SUBSTRING((
  
	 SELECT  isnull(StarFormat,'') + Value+ isnull(endFormat,'')
    FROM FinalTable 
    WHERE ([InputID] = Results.[InputID] and DkFeatureId = Results.DkFeatureId) order by value desc 
	FOR XML PATH(''),TYPE).value('.', 'NVARCHAR(MAX)')
	   
  ,1,5000) AS Value
FROM FinalTable Results
where StatusId=3
)t
select * from #FF

image
image794×129 4.59 KB