What this statement do in SQL server 2012?

Transact-SQL
,
1

I read this SQL statement but i don't understand what this done

select * INTO #FF  from  (
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,DKFeatureName,[Variant Number],IsUnit,
  SUBSTRING((
    -- SELECT  isnull(StarFormat,''),Value + CAST([Separator] AS VARCHAR(100)) , isnull(endFormat,'')
	 SELECT  isnull(StarFormat,'') + Value+ isnull(endFormat,'')
    FROM #FinalTable 
    WHERE ([InputID] = Results.[InputID] and DkFeatureId = Results.DkFeatureId) 
    FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
  ,1,5000) AS Value--,Results.accepted_value_value
FROM #FinalTable Results
where StatusId=3
-- GROUP BY [InputID] ,PART_ID,[Vendor ID],Manufacturer,[Digi-Key Part No.] ,[Mfr Part No.],[Description],Category,Family ,Obsolete ,[Non-Stock] ,	[Part_Status],partNumber,CompanyName,DKFeatureName,DkFeatureId,[Variant Number],IsUnit--,Results.accepted_value_value
union
SELECT 
  [InputID],PART_ID,[Vendor ID],Manufacturer,[Digi-Key Part No.] ,[Mfr Part No.],[Description],Category,Family ,Obsolete ,[Non-Stock] ,
  	[Part_Status],partNumber,CompanyName,DKFeatureName,[Variant Number],IsUnit,Value/*,accepted_value_value Value*/ from #FinalTable where StatusId<>3 
  )T

I ask about why using here xml and why using sub string
and what is meaning .value('(./text())[1]','VARCHAR(MAX)')

1 Like
2

Hi

-- union combines 2 result sets and does distinct ( if there are duplicates ) and order by

-- what he is doing
select result set 1
union
select result set 2

for xml path is used to prepare data in a certain format
one example is comma seperated values
for example if you have rows
a
b
c
for xml path is used to created comma seperated string a,b,c in a single row