SQL Server - query to join two tables based on columnName field
I have an existing SQL Server table structure such as:
--Entries Table--
*Company* | *MusicProduct* | *FoodProduct* | *PCProduct* | *PhoneProduct* |
Apple iPod null iMac iPhone
Dell null null Dellbook null
Primary key in the above table 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 is in the above table ColumnName
, and it contains a row for each of the non-key columns in the first table. This certainly sounds like bad design, but it bodes well for my work.
Basically I want the query to return question and answer rows, ignoring zero answer questions for the selected individual company. The result for Apple will look like this (three lines):
Question Answer
What mp3 device the company known for? iPod
What PC? iMac
What phone does the company give employees? iPhone
How can I achieve this with the above? Could some kind of new reference table go?
source to share
Since you have a fixed number of columns in the records table, you can use an expression CASE
to match from the ColumnName column in the Questions table to the returned column:
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 CTE allows you to use it Answer
in a condition WHERE
, avoiding repeating a long expression CASE
in a sentence WHERE
, which can cause errors in case you need to change something in it.
source to share
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;
source to share