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 |
ahmed elbarbaryPosted Nov 5, 2021, 4:46 PM
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
Sachin SinghPosted Nov 5, 2021, 4:15 PM
ahmed elbarbaryPosted Nov 5, 2021, 3:29 PM
ahmed elbarbaryPosted Nov 5, 2021, 2:51 PM
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 RamamoorthiPosted Nov 5, 2021, 1:38 PM