February 8, 2021 at 6:18 am
I work on SQL server 2012 I face issue i can't display feature size on final result
query
this happen when replace temp table partc and part x not have value to same feature as size feature
but if part c and partx have same feature no problem it is ok
as example below :
DROP TABLE IF EXISTS #replace
DROP TABLE IF EXISTS #FeatureNameandValues
DROP TABLE IF EXISTS #Temp
DROP TABLE IF EXISTS #Temp1
DROP TABLE IF EXISTS #Temp2
create table #replace
(
PartIdc int,
PartIdx int,
)
insert into #replace(PartIdc,PartIdx)
values
(1211,1300),
(2000,2200),
(3000,3100),
(4150,4200)
create table #FeatureNameandValues
(
PartId int,
[FeatureName] nvarchar(20),
[FeatureValue] int
)
insert into #FeatureNameandValues(PartId,[FeatureName],[FeatureValue])
values
(1211,'Weight',5),
(2000,'Tall',20),
(3000,'Weight',70),
(4150,'Tall',190),
(1211,'Tall',80),
(1300,'Weight',10),
(3100,'Size',150),
(4200,'Tall',130),
(1300,'Tall',20)
SELECT a.[FeatureName] [FeatureName], CASE WHEN a.PartId = b.PartIdc THEN 1 WHEN a.PartId=b.PartIdx THEN 2 END PartOrder, b.PartIdc PartC,b.PartIdx PartX, a.[FeatureValue] [FeatureValue]
INTO #Temp
FROM #FeatureNameandValues a
JOIN #replace b ON a.PartId = b.PartIdc OR a.PartId = b.PartIdx
-- Find out different values
-- If value belongs to PartC, then order = 1; PartX, order = 2
-- So that the feature value for c will be the former one
SELECT a.[FeatureName] [FeatureName], a.PartOrder, a.PartC PartC, a.PartX PartX, a.[FeatureValue] [FeatureValue]
INTO #Temp1
FROM #Temp a
JOIN #Temp b ON a.FeatureName=b.FeatureName AND a.PartC=b.PartC AND a.PartX=b.PartX AND a.[FeatureValue] <> b.[FeatureValue]
-- Display the result for different values
SELECT * FROM #Temp1
ORDER BY PartC,PartX,[FeatureName],PartOrder
-- Concatenate the values for each group
SELECT T1.[FeatureName], T1.PartC, T1.PartX,
STUFF(
(
SELECT '-' + CAST(T2.[FeatureValue] AS VARCHAR(MAX))
FROM #Temp1 T2
WHERE T1.[FeatureName] = T2.[FeatureName] AND T1.PartC = T2.PartC AND T1.PartX = T2.PartX
FOR XML PATH ('')
),1,1,'') [Difference]
INTO #Temp2
FROM #Temp1 T1
GROUP BY T1.PartC,T1.PartX,T1.[FeatureName]
SELECT * FROM #Temp2
-- Out one row
SELECT STUFF(
(SELECT ' | ' + [FeatureName] + '( '+ [Difference] + ' )' FROM #Temp2 FOR XML PATH('')),
1,2,'') AS [Result]
i need when have feature but not have value to it to display as Null
tall is null for part x because partx(3100) not have value for feature tall
size is Null for partc (2000) because not have value for feature size for partc
final result expected is :
Weight(5-10) | Tall (20-NULL) | size(NULL-150) | Tall(190-130)
create table #replace
(
PartIdc int,
PartIdx int,
)
insert into #replace(PartIdc,PartIdx)
values
(1211,1300), Weight(5-10)
(2000,2200), Tall (20-NULL)
(3000,3100), size(NULL-150)
(4150,4200) Tall(190-130)
wrong result is
Tall (80-20) | Weight(5-10) | Tall(190-130)
February 8, 2021 at 5:40 pm
How do you know what features are there or missing? Is there a list of features?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply