How to get Feature Name and Feature Value separated by $ for table All

Question

I work on SQL server 2012 I face issue I can't get Feature Name and Feature Value for

Table All Data From table Part Attributes

Feature Name and Feature Value exist on table Part Attributes

I attached Table structure with post

Expected result when make

select * from dbo.AllData

will be

PartID	Code	CodeTypeID	RevisionID	ZPLID	ConCount	FeatureName	FeatureValue
413989	32111502	1809942	329888149	4239	1	Type	Zener
413989	8541100000	1019997	313023938	4239	1	Product Shape Type	Discrete
413989	8541100050	849774	307683692	4239	3	Product Shape Type$Package Family$Type	Discrete$SOT$Zener

Sachin Singh · Answer

Test this select * into #temp1 from (select PartId,[Code],[CodeTypeID],[RevisionID],[ZPLID],FeatureName,FeatureValue,g.[ZfeatureKey] from [dbo].[gen] g inner join PartAttributes pa on g.[ZfeatureKey]=pa.[ZfeatureKey]) tempp SELECT Distinct PartId,[Code],[CodeTypeID],[RevisionID],[ZPLID], STUFF( (SELECT DISTINCT '$' + FeatureName FROM #temp1 WHERE [CodeTypeID] = a.[CodeTypeID] FOR XML PATH ( '' )) , 1, 1, '' ) AS FeatureName, STUFF( (SELECT DISTINCT '$' + FeatureValue FROM #temp1 WHERE [CodeTypeID] = a.[CodeTypeID] FOR XML PATH ( '' )) , 1, 1, '' ) AS FeatureValue FROM #temp1 AS a drop table #temp1

ahmed elbarbary · Answer

ok this is good and near from what i need but can i modify below statment by add feature name and feature value SET @Sql= CONCAT('INSERT INTO dbo.AllData(PartID,Code,CodeTypeID,RevisionID,ZPLID ,ConCount)' ,' SELECT PartID,Code,Co.CodeTypeID,Co.RevisionID,Co.ZPLID,Count(1) as ConCount FROM PartAttributes PM INNER JOIN Condition Co ON Co.ZfeatureKey = PM.ZfeatureKey ' , 'Where (1=1 and ',@Con , @ConStr,' ) Group By PartID,Code,Co.CodeTypeID,Co.RevisionID,Co.ZPLID ' , ' Having Count(1)>= ',(SELECT COUNT(1) FROM Condition)) EXEC (@SQL) How to add Feature name and feature value stuff to result above full script attached with post

ahmed elbarbary · Answer

full script sql structure and query as below https://www.mycompiler.io/view/3LncvaR

ahmed elbarbary · Answer

so can you help me to do that I need to modify statment below to stuff value on featurename and featurevalue for all data table UPDATE Codes SET Proceed=0 DECLARE @Code VARCHAR(20) DECLARE @ZPID INT DECLARE @Sql nvarchar(max) DECLARE @Con nvarchar(max) DECLARE @ConStr nvarchar(max) WHILE (Select Count(*) From Codes with(nolock) where Proceed =0 ) > 0 BEGIN SELECT Top 1 @ZPID=ZPLID, @Code=Code From Codes with(nolock) where Proceed=0 SELECT * INTO Condition FROM Gen G with(nolock) WHERE g.Code=@Code AND G.ZPLID=@ZPID AND G.ZfeatureKey IS NOT NULL SET @Con= STUFF((SELECT CONCAT(' Or (PM.ZfeatureKey= ', CC.ZfeatureKey , ' And AcceptedValuesOption_Value ' , CAST(EStrat AS NVARCHAR(2500)) , IIF(EEnd='','',CONCAT(' And AcceptedValuesOption_Value ',EEnd)),')') FROM Condition CC INNER JOIN Allfeatures AL with(nolock) ON AL.ZfeatureKey = CC.ZfeatureKey AND AL.IsNumericValues >0 FOR XML PATH(''), TYPE).value('(./text())[1]','varchar(max)'),1,3,'') SET @ConStr= STUFF((SELECT CONCAT(' Or (PM.ZfeatureKey= ', CC.ZfeatureKey , IIF(CC.ZfeatureType='Qualifications',' And AcceptedValuesOption_Value ' , ' And FeatureValue ' ) , CAST(EStrat AS NVARCHAR(2500)),')') --ValueName FROM Condition CC INNER JOIN Allfeatures AL with(nolock) ON AL.ZfeatureKey = CC.ZfeatureKey AND AL.IsNumericValues =0 FOR XML PATH(''), TYPE).value('(./text())[1]','varchar(max)'),1,3,'') SET @ConStr = IIF(LEN(@Con)>3 AND LEN(@ConStr)>3 , CONCAT('Or ',@ConStr),@ConStr ) SET @Sql= CONCAT('INSERT INTO dbo.AllData(PartID,Code,CodeTypeID,RevisionID,ZPLID ,ConCount)',' SELECT PartID,Code,Co.CodeTypeID,Co.RevisionID,Co.ZPLID,Count(1) as ConCount FROM PartAttributes PM INNER JOIN Condition Co ON Co.ZfeatureKey = PM.ZfeatureKey ', 'Where (1=1 and ',@Con , @ConStr,' ) Group By PartID,Code,Co.CodeTypeID,Co.RevisionID,Co.ZPLID ' , ' Having Count(1)>= ',(SELECT COUNT(1) FROM Condition)) EXEC (@SQL) DROP TABLE Condition UPDATE Codes Set Proceed = 1 Where @ZPID=ZPLID AND Code=@Code END also structure attached with origina post

Srinivasan Ramamoorthi · Answer

You can use a cursor, and separate the columnvalues using substring function.