Special Grouping - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Special Grouping

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources