CODEWITHSUNDEEP
sqljoin
Samatag
Samatag

Reputation: 15

Join column name to the value in another column

I have the following tables:

Category Table

> ID    Code    CodeValue    Value 
> 1     sex     1            Male 
> 1     sex     2            Female 
> 2     age     1            Under 20 
> 2     age     2            Between 20 and 40 
> 2     age     3            Over 40

Data Table

> sex    age  
> 1      1
> 1      2
> 2      2
> 1      3

I want to return the Value of a field based on the Code, so that the final table would like something like below:

> sex      age  
> Male     Under 20
> Male     Between 20 and 40
> Female   Between 20 and 40
> Male     Over 40

Is there any way to join the values (sex age) of the 'Code' column in the Category Table with the column names in the Data table, and then return the corresponding 'Value' (Male, Female, Under 20 etc...)

Thanks!

Sam

Upvotes: 0

Views: 429

Answers (1)

clinomaniac
clinomaniac

Reputation: 2218

You can join the table twice, once for each category. 

SELECT cts.value sex, cta.value age
FROM data_table dt
INNER JOIN category_table cts
  ON dt.sex = cts.code_value AND cts.code = 'sex'
INNER JOIN category_table cta
  ON dt.age = cta.code_value AND cta.code = 'age'

Upvotes: 2

Related Questions