sql - Dynamically Join Enum Table with other Table -

sql - Dynamically Join Enum Table with other Table -

i have next table. these not real tables concept here.

table1 ---------------------------------- fieldid|enumeratedvalue|textvalue ---------------------------------- col1 |1 |hey ---------------------------------- col1 |2 |test ---------------------------------- col1 |3 |george ---------------------------------- col2 |1 |random ---------------------------------- col2 |2 |wesley ---------------------------------- col3 |1 |tompson ---------------------------------- col3 |2 |oooo ---------------------------------- table2 ---------------------------------- col1 |col2 |col3 ---------------------------------- 1 |2 |1 ---------------------------------- 2 |1 |1 ---------------------------------- 3 |1 |2 ----------------------------------

the desired result view

---------------------------------- col1 |col2 |col3 ---------------------------------- hey |wesley |tompson ---------------------------------- test |random |tompson ---------------------------------- george |random |oooo ----------------------------------

so write like

select col1.textvalue,col2.textvalue,col3.textvalue table2 t2, (select * table1 fieldid = 'col1') col1, (select * table1 fieldid = 'col2') col2, (select * table1 fieldid = 'col3') col3 t2.col1 = col1.enumeratedvalue , t2.col2 = col2.enumeratedvalue , t2.col3 = col3.enumeratedvalue

the problem in our real tables there ~20 of these columns per table. find simpler means of writing code. not want hard code 20 joins each view making. allow me know if there alternatives.

as variable bring together solution, dynamic pivot improve way @ this. it's not performance hard-coded, non-pivot solution, involve to the lowest degree development hours, , can adapted generate view definitions rather made table-valued function (which fed table name , perchance column names pivot , join)

first, demo info setup:

if object_id('dbo.test_enum') not null drop table dbo.test_enum create table dbo.test_enum ( fieldid sysname , enumeratedvalue int not null , textvalue sysname , primary key (fieldid, enumeratedvalue) ) insert dbo.test_enum (fieldid, enumeratedvalue, textvalue) values ('col1', 1, 'hey'), ('col1', 2, 'test'), ('col1', 3, 'george'), ('col2', 1, 'random'), ('col2', 2, 'wesley'), ('col3', 1, 'tompson'), ('col3', 2, 'oooo') ; if object_id('dbo.test_table') not null drop table dbo.test_table create table test_table ( id int primary key identity(1,1) , col1 int not null , col2 int not null , col3 int not null ); insert dbo.test_table (col1, col2, col3) values (1,2,1), (2,1,1), (3,1,2)

next dynamic selection part:

declare @cols nvarchar(max) = (select stuff(( select ',' + quotename(c.name) sys.objects o inner bring together sys.columns c on c.object_id = o.object_id o.name = 'test_table' , c.name in ('col1', 'col2', 'col3') xml path ('') ),1,1,'')) declare @sql nvarchar(max) = n' select p.id, ' + @cols + ' ( select unp.id, unp.fieldid, e.textvalue dbo.test_table unpivot ( enumeratedvalues fieldid in (' + @cols + ') ) unp inner bring together dbo.test_enum e on e.enumeratedvalue = unp.enumeratedvalues , e.fieldid = unp.fieldid ) z pivot ( min(z.textvalue) z.fieldid in (' + @cols + ') ) p ' exec sp_executesql @sql

on own, homecoming result set desired in question, can adapted homecoming view definitions of given table/column info set, , farther adapted per table/view.

sql sql-server sql-server-2008 enums