How to get FeatureName and FeatureValue separated by $ for table AllData ?

[ahmedsa19051985]

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

 IF OBJECT_ID('[dbo].[Codes]') IS NOT NULL
DROP TABLE [dbo].[Codes]    
IF OBJECT_ID('[dbo].[gen]') IS NOT NULL
DROP TABLE [dbo].[gen]  
IF OBJECT_ID('[dbo].[PartAttributes]') IS NOT NULL
DROP TABLE [dbo].[PartAttributes]   

IF OBJECT_ID('[dbo].[Allfeatures]') IS NOT NULL
DROP TABLE [dbo].[Allfeatures]
IF OBJECT_ID('dbo.AllData') IS NOT NULL
DROP TABLE dbo.AllData
IF OBJECT_ID('dbo.Condition') IS NOT NULL
DROP TABLE [dbo].Condition

        CREATE TABLE [dbo].[Codes](
    [ZPLID] [int] NULL,
    [Code] [varchar](20) NULL,
    [Proceed] [int] NOT NULL
) ON [PRIMARY]

GO

INSERT [dbo].[Codes] ([ZPLID], [Code], [Proceed]) VALUES (4239, N'32111502', 1)
INSERT [dbo].[Codes] ([ZPLID], [Code], [Proceed]) VALUES (4239, N'8541100000', 1)
INSERT [dbo].[Codes] ([ZPLID], [Code], [Proceed]) VALUES (4239, N'8541100050', 1)

CREATE TABLE [dbo].[gen](
    [TradeCodeControlID] [int] IDENTITY(1,1) NOT NULL,
    [CodeTypeID] [int] NULL,
    [RevisionID] [bigint] NULL,
    [Code] [varchar](20) NULL,
    [ZPLID] [int] NULL,
    [ZfeatureKey] [bigint] NULL,
    [ZfeatureType] [nvarchar](200) NULL,
    [EStrat] [nvarchar](2500) NULL,
    [EEnd] [nvarchar](2500) NULL
) ON [PRIMARY]


GO
SET IDENTITY_INSERT [dbo].[gen] ON 
INSERT [dbo].[gen] ([TradeCodeControlID], [CodeTypeID],[RevisionID],  [Code], [ZPLID], [ZfeatureKey], [ZfeatureType], [EStrat], [EEnd]) VALUES (7565,  849774, 307683692, N'8541100050', 4239, 202503, N'Package', N'<>''IC''', N'')
INSERT [dbo].[gen] ([TradeCodeControlID], [CodeTypeID],[RevisionID],  [Code], [ZPLID], [ZfeatureKey], [ZfeatureType], [EStrat], [EEnd]) VALUES (7566,  849774, 307683692, N'8541100050', 4239, 202504, N'Package', N'Not In(''Die'',''Wafer'',''N/A'')', N'')
INSERT [dbo].[gen] ([TradeCodeControlID], [CodeTypeID],[RevisionID],   [Code], [ZPLID], [ZfeatureKey], [ZfeatureType], [EStrat], [EEnd]) VALUES (7567,  849774, 307683692, N'8541100050', 4239, 1501170111, NULL, N'=''Zener''', N'')
INSERT [dbo].[gen] ([TradeCodeControlID], [CodeTypeID],[RevisionID],  [Code], [ZPLID], [ZfeatureKey], [ZfeatureType], [EStrat], [EEnd]) VALUES (7577, 1019997, 313023938, N'8541100000', 4239, 202503, N'Package', N'<>''IC''', N'')
INSERT [dbo].[gen] ([TradeCodeControlID], [CodeTypeID],[RevisionID],  [Code], [ZPLID], [ZfeatureKey], [ZfeatureType], [EStrat], [EEnd]) VALUES (9497, 1809942, 329888149, N'32111502', 4239, 1501170111, NULL, N'=''Zener''', N'')

SET IDENTITY_INSERT [dbo].[gen] OFF

CREATE TABLE [dbo].[PartAttributes](
    [PartID] [int] NOT NULL,
    [ZfeatureKey] [bigint] NULL,
    [FeatureName] [nvarchar](200) NOT NULL,
    [AcceptedValuesOption_Value] [float] NULL,
    [FeatureValue] [nvarchar](500) NOT NULL
) ON [PRIMARY]

GO
INSERT [dbo].[PartAttributes] ([PartID], [ZfeatureKey],[FeatureName], [AcceptedValuesOption_Value], [FeatureValue]) VALUES (413989, 202503,'Product Shape Type', NULL, N'Discrete')
INSERT [dbo].[PartAttributes] ([PartID], [ZfeatureKey],[FeatureName], [AcceptedValuesOption_Value], [FeatureValue]) VALUES (413989, 1501170111,'Type', NULL, N'Zener')
INSERT [dbo].[PartAttributes] ([PartID], [ZfeatureKey],[FeatureName], [AcceptedValuesOption_Value], [FeatureValue]) VALUES (413989, 1501170046,'Maximum Peak Pulse Current', 3, N'3A')
INSERT [dbo].[PartAttributes] ([PartID], [ZfeatureKey],[FeatureName], [AcceptedValuesOption_Value], [FeatureValue]) VALUES (413989, 202504,'Package Family', NULL, N'SOT')

CREATE TABLE [dbo].[Allfeatures](
    [ZPLID] [int] NULL,
    [ZfeatureKey] [bigint] NULL,
    [FeatType] [int] NULL,
    [AcceptedValueID] [int] NULL,
    [IsNumericValues] [int] NULL
) ON [PRIMARY]

GO
INSERT [dbo].[Allfeatures] ([ZPLID], [ZfeatureKey], [FeatType], [AcceptedValueID], [IsNumericValues]) VALUES (4239, NULL, 0, 0, 0)
INSERT [dbo].[Allfeatures] ([ZPLID], [ZfeatureKey], [FeatType], [AcceptedValueID], [IsNumericValues]) VALUES (4239, 202503, NULL, 33, 0)
INSERT [dbo].[Allfeatures] ([ZPLID], [ZfeatureKey], [FeatType], [AcceptedValueID], [IsNumericValues]) VALUES (4239, 202504, NULL, 34, 0)
INSERT [dbo].[Allfeatures] ([ZPLID], [ZfeatureKey], [FeatType], [AcceptedValueID], [IsNumericValues]) VALUES (4239, 1501170046, 2044, 814, 1)
INSERT [dbo].[Allfeatures] ([ZPLID], [ZfeatureKey], [FeatType], [AcceptedValueID], [IsNumericValues]) VALUES (4239, 1501170111, 2043, 155, 0)


        CREATE TABLE dbo.AllData
        (

        PartID INT, 
        Code VARCHAR(20),   
        CodeTypeID INT, 
        RevisionID BIGINT,  
        ZPLID INT,  
        ConCount INT,
        FeatureName nvarchar(500),
        FeatureValue  nvarchar(500)

        )

        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

expected result as below