Avatar of tmajor99
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

 
SQL

Avatar of undefined
Last Comment
Sharath S
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
;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 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


Avatar of Sharath S
Sharath S
Flag of United States of America image

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)


Open in new window

SQL
SQL

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.

61K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo