tmajor99
asked on
SQL Select - Group by values from horizontal table alignment to Vertical
I need to list our values from Table A that are not in Table B. Table A contains a list of all attributes and their values from products. These attribute values need to be compared to table B which just contains a list of all valid values for each attribute. Each value in table A is listed a separate column; Value1; Value 2; Value3 and Value 4. Each of these values from each of these columns needs to be compare with Table B and then report the values not found in a vertical alignment when each attribute and value are a separate row.
Table A (List of Products with values)
AttributeID Value 1 Value2 Value3 Value4
Color Black White Pink
MaterialType Wood Plastic
Finish Glossy Satin Other
Table B (List Valid Values)
AttributeID AttributeValue
Color Black
Color Blue
Color Orange
MaterialType Plastic
Finish Satin
Finish Primed
Report Expectations (only list the attribute values not found in table B)
AttributeID Value
Color White
Color Pink
MaterialType Wood
Finish Glossy
Finish Other
Table A (List of Products with values)
AttributeID Value 1 Value2 Value3 Value4
Color Black White Pink
MaterialType Wood Plastic
Finish Glossy Satin Other
Table B (List Valid Values)
AttributeID AttributeValue
Color Black
Color Blue
Color Orange
MaterialType Plastic
Finish Satin
Finish Primed
Report Expectations (only list the attribute values not found in table B)
AttributeID Value
Color White
Color Pink
MaterialType Wood
Finish Glossy
Finish Other
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I like cross apply as Scott Pletcher suggested. Here is another approach using JOIN.
;WITH sample_data AS (
SELECT * FROM ( VALUES
('Color', 'Black', 'White', '', 'Pink'),
('MaterialType', 'Wood', '', 'Plastic', ''),
('Finish', '', 'Glossy', 'Satin', 'Other') ) AS sd(AttributeID, Value1, Value2, Value3, Value4)
)
select *
from (
select AttributeID,
case rn when 1 then Value1 when 2 then Value2 when 3 then Value3 when 4 then Value4 end Value
from sample_data, (select * from (values (1), (2), (3), (4)) as t(rn)) t1) as cal
where ca1.Value <> ''
and NOT EXISTS(SELECT 1 FROM dbo.TableB B WHERE B.AtttributeID = ca1.AttributeID AND B.Value = ca1.Value)
SELECT * FROM ( VALUES
('Color', 'Black', 'White', '', 'Pink'),
('MaterialType', 'Wood', '', 'Plastic', ''),
('Finish', '', 'Glossy', 'Satin', 'Other') ) AS sd(AttributeID, Value1, Value2, Value3, Value4)
)
SELECT ca1.*
FROM sample_data
CROSS APPLY ( VALUES
(AttributeID, Value1), (AttributeID, Value2), (AttributeID, Value3), (AttributeID, Value4)
) AS ca1(AttributeID, Value)
WHERE ca1.Value <> '' AND
NOT EXISTS(SELECT 1 FROM dbo.TableB B WHERE B.AtttributeID = ca1.AttributeID AND B.Value = ca1.Value)
ORDER BY ca1.AttributeID, ca1.Value