Author |
Topic |
Vivan
Starting Member
9 Posts |
Posted - 2015-04-19 : 12:49:02
|
Please help me to solve this puzzle
DECLARE @MYTABLE TABLE (
[Part Type] VARCHAR (50), [Desc 1] VARCHAR (50), [Desc 2] VARCHAR (50), [Desc 3] VARCHAR (50), [Desc 4] VARCHAR (50), [Desc 5] VARCHAR (50) )
INSERT @MYTABLE
SELECT 'Nerf Bar', 'Step Type', 'Finish', 'Color', 'Material', 'Type' UNION ALL SELECT 'Nerf Bar', 'Step Type', 'Finish', 'Color', 'Material', 'Type' UNION ALL SELECT 'Nerf Bar', 'Step Type', 'Finish', 'Color', 'Material', 'Type' UNION ALL SELECT 'Nerf Bar', 'Step Type', 'Finish', 'Color', '', 'Type' UNION ALL SELECT 'Nerf Bar', 'Step Type', 'Start' , 'Color', 'Material', 'Type' UNION ALL SELECT 'Shifter Boot', 'Compatibility', 'Diameter (IN)', 'Base Length (IN)', 'Base Width (IN)','Color' UNION ALL SELECT 'Shifter Boot', 'Compatibility', 'Diameter (IN)', 'Base Length (IN)' ,'Base Width (IN)', 'Color' UNION ALL SELECT 'Shifter Boot', 'Not Compatible', 'Diameter (IN)', 'Base Length (IN)', 'Base Width (IN)', 'Color' UNION ALL SELECT 'Shifter Boot', 'Compatibility', 'Diameter (IN)', 'Base Length UK', 'Base Width (IN)', 'Color' UNION ALL SELECT 'Tail Light', 'Used', 'Lens Color', 'Reflector', 'With Seal' ,'Hardware' UNION ALL SELECT 'ACCELATOR', 'Compatibility' ,'Base Diameter (IN)', 'Base Length (IN)', 'Base Width (IN)', 'Color' UNION ALL SELECT 'ACCELATOR', 'Compatibility', 'Base Diameter (IN)', 'Base Length (IN)' ,'Base Width (IN)', 'Color'
SELECT * FROM @MYTABLE
/*
I am trying to get solution for this complicated Scenerio,
for a given Part Type we have DESC 1 TO DESC 5
1st: I need to check There will be always More Than 1 part type , SO In this case Nerf Bar and Shifter Boot are the two Part Type which Occurred More Than 1 time
Tail Light Part Type is occurred only once, so ignore
2nd: I need to check For a Given Part Type there will be same value contain in desc 1 to desc 5 across rows if any changes or mismatch then it should appear in Output
For Part Type Accelator from desc 1 to desc 5 same records for both rows so ignored.
Output Look Like
'Nerf Bar', 'Step Type', 'Finish', 'Color', 'Material', 'Type' 'Nerf Bar', 'Step Type', 'Finish', 'Color', '', 'Type' 'Nerf Bar', 'Step Type', 'Start' , 'Color', 'Material', 'Type'
'Shifter Boot', 'Compatibility', 'Diameter (IN)', 'Base Length (IN)' ,'Base Width (IN)', 'Color' 'Shifter Boot', 'Not Compatible', 'Diameter (IN)', 'Base Length (IN)', 'Base Width (IN)', 'Color' 'Shifter Boot', 'Compatibility', 'Diameter (IN)', 'Base Length UK', 'Base Width (IN)', 'Color'
*/
viva |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2015-04-19 : 13:30:05
|
[code]-- SwePeso WITH cteData AS ( SELECT [Part Type], [Desc 1], [Desc 2], [Desc 3], [Desc 4], [Desc 5], COUNT(*) OVER (PARTITION BY [Part Type]) AS rn FROM ( SELECT DISTINCT [Part Type], [Desc 1], [Desc 2], [Desc 3], [Desc 4], [Desc 5] FROM @MyTable ) AS x ) SELECT [Part Type], [Desc 1], [Desc 2], [Desc 3], [Desc 4], [Desc 5] FROM cteData WHERE rn >= 2;[/code]
Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
 |
|
|
|
|