join table with translation table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

join table with translation table

Author

Topic

generalya
Starting Member

2 Posts

Posted - 2012-08-21 : 20:24:15

I have two tables as such:

ID Name indicator_1 indicator_2
1 text 1 1
2 text 1 1
3 text 1 1
4 text 2 1
5 text 2 1
6 text 2 2
7 text 2 2
8 text 3 2

FieldName Value Description
indicator_1 1 good
indicator_1 2 medium
indicator_1 3 bad
indicator_2 1 yes
indicator_2 2 no

Is there any way to join the two tables and arrive at:

ID Name indicator_1 indicator_2
1 text good yes
2 text good yes
3 text good yes
4 text medium yes
5 text medium yes
6 text medium no
7 text medium no
8 text bad no

This is just a sample, the actual table has about 70 indicators...thanks much

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-21 : 20:41:48

yep

you need to join them separately like


SELECT t1.ID,t1.Name,t2.Description AS indicator_1,t3.Description AS Indicator_2
FROM table1 t1
INNER JOIN table2 t2
ON t2.Value = t1.indicator_1
AND t2.FieldName = 'Indicator_1'
INNER JOIN table2 t3
ON t3.Value = t1.indicator_2
AND t3.FieldName = 'Indicator_2'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-21 : 20:48:03

another way is


SELECT *
FROM
(
SELECT m.ID, m.Name,m.Category,t2.Description
FROM
(
SELECT *
FROM table1 t1
UNPIVOT (Value FOR Category IN ([indicator_1],[indicator_2],..))u
)m
INNER JOIN table2 t2
ON t2.FieldName = m.Category
AND t2.Value = m.Value
)p
PIVOT (MAX(Description) FOR Category IN ([indicator_1],[indicator_2],..))q

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

generalya
Starting Member

2 Posts

Posted - 2012-08-22 : 00:23:16

Thanks, I am interested in the below query...but newbie question: I don't have a column called category, how does the inner select (UNPIVOT VALUE FOR CATEGORY) work?

quote:
Originally posted by visakh16

another way is

SELECT *
FROM
(
SELECT m.ID, m.Name,m.Category,t2.Description
FROM
(
SELECT *
FROM table1 t1
UNPIVOT (Value FOR Category IN ([indicator_1],[indicator_2],..))u
)m
INNER JOIN table2 t2
ON t2.FieldName = m.Category
AND t2.Value = m.Value
)p
PIVOT (MAX(Description) FOR Category IN ([indicator_1],[indicator_2],..))q

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-22 : 00:32:28

Category column will be created on the fly. you just need to have columns as shown in your sample

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources