CODEWITHSUNDEEP
sqlsql-server
AliceSmith
AliceSmith

Reputation: 371

SQL Server - Query to join two tables based on columnName field

I have an existing SQL Server table structure like the following:

--Entries Table--
*Company* | *MusicProduct* | *FoodProduct* | *PCProduct* | *PhoneProduct* |
  Apple         iPod             null           iMac           iPhone
  Dell          null             null          Dellbook         null

The Primary Key on the above table is Company

--Questions Table--
*ColumnName*    |   Questions
 MusicProduct        What mp3 device the company known for?
 FoodProduct         What food product describes the company best?
 PCProduct           What PC?
 PhoneProduct        What phone does the company give employees?

The Primary Key on the above table is ColumnName and it contains a row for each of the non-key columns in the first table. This certainly feels like bad design, but it preexists my work.

I basically want a query that will return rows with a question and answer, ignoring questions with null answers, for a chosen single company. The output for Apple would look like this (three rows):

  Question                                        Answer
  What mp3 device the company known for?          iPod
  What PC?                                        iMac
  What phone does the company give employees?     iPhone

How can I best accomplish this with the above? Would some sort of new reference table be the way to go?

Upvotes: 4

Views: 1672

Answers (2)

FrankPl
FrankPl

Reputation: 13315

As you have a fix number of columns in your entries table, you can use a CASE expression to map from the ColumnName column in the Questions table to the column to return:

WITH a as(
     SELECT q.Question
          ,CASE q.ColumnName
                WHEN 'MusicProduct' THEN e.MusicProduct
                WHEN 'FoodProduct' THEN e.FoodProduct
                WHEN 'PCProduct' THEN e.PCProduct
                WHEN 'PhoneProduct' THEN e.PhoneProduct
           END as Answer
      FROM Questions q
           CROSS JOIN Entries
     WHERE q.Company = 'Apple'
   )
 SELECT Question, Answer
   FROM a
  WHERE Answer IS NOT NULL

Using a CTE allows you to use Answer in the WHERE condition, avoiding to have to repeat the long CASE expression in the WHERE clause, which could cause errors in case you need to change anything in it.

Upvotes: 1

sqlhdv
sqlhdv

Reputation: 254

Please try this.

select q.question, product as answer
from
(select company, Musicproduct, Foodproduct, PCProduct, phoneproduct from Entries ) p
unpivot ( product for Products in (Musicproduct, Foodproduct, PCProduct, phoneproduct)
) unpvt
join questions q on unpvt.product = q.answer;

Upvotes: 2

Related Questions