How To get different feature values on one row separated stick if it multiple d
-
I work on SQL server 2012 I face issue : I can't get all different value Feature one one row result
separated by sticky if it multiple difference
if one different then no need stick.
where c have value and x have value but both not equal each other
so How can i do that Please ?
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,
FeatueName nvarchar(20),
FeaatureValue int
)
insert into #FeatureNameandValues(PartId,FeatueName,FeaatureValue)
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)Expected Result
Tall (80-20) | Weight(5-10) | Tall(190-130)
and file excel attached ;
http://www.mediafire.com/file/mxyr8wr9k98za7o/ExplainReport.xlsx/file
-
Could you provide:
1 - what you have tried already?
2 - sample output for what it should look like?
I am not sure what "stick" means or what your output should look like. For example, I came up with this query:
USE [Admin];
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
, [FeatueName] NVARCHAR(20)
, [FeaatureValue] INT
);
INSERT INTO [#FeatureNameandValues]
(
[PartId]
, [FeatueName]
, [FeaatureValue]
)
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
);
WITH [SourceData]
AS
(
SELECT
[PartId]
, [FeatueName]
, [FeaatureValue]
, [PartIdc]
, [PartIdx]
FROM[#FeatureNameandValues]
LEFT JOIN[#replace]
ON [PartId] = [PartIdc]
)
SELECT
[CData].[FeatueName]
, [CData].[PartId]AS [PartC]
, [XData].[PartId]AS [PartX]
, [CData].[FeaatureValue]AS [FeatureValueC]
, [XData].[FeaatureValue]AS [FeatureValueX]
, NULLIF([CData].[FeaatureValue] - [XData].[FeaatureValue],0) AS [FeatureValue(C-X)]
FROM[SourceData] AS [CData]
JOIN[SourceData] AS [XData]
ON [CData].[PartIdx] = [XData].[PartId]
AND [XData].[FeatueName] = [CData].[FeatueName];
DROP TABLE [#replace];
DROP TABLE [#FeatureNameandValues];which gives you the FeatureValue(C-X) that you were asking for where it exists, but I didn't know what to do with the items that do not match up for a PartC and PartX such as 3000 which has a WEIGHT but no 3100 for WEIGHT, only a SIZE for 3100. And SIZE for 3100 has no corresponding 3000.
I did not download the Excel as I don't think that would be too helpful and I am hesitant to download things like that from the internet.
EDIT - I misread the post. You basically want to combine the 3 rows and results into 1. So that can be done with the above query but change the final SELECT (the bit just above the DROP TABLEs section) to:
SELECT STUFF(( SELECT '|' +
[CData].[FeatueName] +'('+ CAST([CData].[FeaatureValue] AS VARCHAR(255)) + '-' + CAST([XData].[FeaatureValue] AS VARCHAR(255)) +')'
FROM[SourceData] AS [CData]
JOIN[SourceData] AS [XData]
ON [CData].[PartIdx] = [XData].[PartId]
AND [XData].[FeatueName] = [CData].[FeatueName] FOR XML PATH('')),1,1,'') AS Result;The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system. -
This was removed by the editor as SPAM
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply