How to get Feature Name and Feature Value separated by $ for table AllData ?
-
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
full sql structure and query
found here
https://www.mycompiler.io/view/3LncvaR
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
ENDI attached Table structure with post
Expected result when make
select * from dbo.AllData
will be
- This topic was modified 3 years ago by ahmed_elbarbary.2010. Reason: add more details
-
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
-
I was careful to preserve as much of the original code as possible and to document this as well as the original code was. 😉
UPDATE Codes
SET
Proceed=0
DROP TABLE IF EXISTS #Condition;
DROP TABLE IF EXISTS #Results;
SELECT *
INTO #Results
FROM dbo.AllData
WHERE 1 = 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 #Results
(PartID,Code, CodeTypeID , RevisionID , ZPLID , FeatureName , FeatureValue , ConCount)
SELECT PartID,Code, Co.CodeTypeID, Co.RevisionID, Co.ZPLID, pm.FeatureName, pm.FeatureValue
,ConCount = ROW_NUMBER()OVER(ORDER BY PM.FeatureName)
FROM dbo.PartAttributes pm
INNER JOIN #Condition co ON co.ZfeatureKey = PM.ZfeatureKey
WHERE (1=1 AND ',@Con , @ConStr,' );')
;
EXEC (@SQL);
DROP TABLE #Condition
UPDATE Codes SET Proceed = 1 WHERE @ZPID=ZPLID AND Code=@Code
END
INSERT INTO dbo.AllData
(PartID,Code,CodeTypeID,RevisionID,ZPLID,ConCount,FeatureName,FeatureValue)
SELECT PartID,Code,CodeTypeID,RevisionID,ZPLID
,ConCount = MAX(ConCount)
,FeatureName = STUFF(
(SELECT '$' + r2.FeatureName
FROM #Results r2
WHERE r2.PartID = r1.PartID
AND r2.Code = r1.Code
AND r2.CodeTypeID = r1.CodeTypeID
AND r2.RevisionID = r1.RevisionID
AND r2.ZPLID = r1.ZPLID
ORDER BY r2.FeatureName
FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)'),1,1,'')
,FeatureValue = STUFF(
(SELECT '$' + r2.FeatureValue
FROM #Results r2
WHERE r2.PartID = r1.PartID
AND r2.Code = r1.Code
AND r2.CodeTypeID = r1.CodeTypeID
AND r2.RevisionID = r1.RevisionID
AND r2.ZPLID = r1.ZPLID
ORDER BY r2.FeatureName
FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)'),1,1,'')
FROM dbo.#Results r1
GROUP BY PartID,Code,CodeTypeID,RevisionID,ZPLID
;
SELECT *
FROM dbo.AllData
;--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply