2

I'm working with a legacy DB that has a table that houses field names from other tables.

So I have this structure:

Field_ID | Field_Name
*********************
1 | Col1  
2 | Col2  
3 | Col3  
4 | Col4

and I need to pull a list of this field metadata along with the values of that field for a given user. So I need:

Field_ID | Field_Name | Value  
1 | Col1 | ValueOfCol1onADiffTable  
2 | Col2 | ValueOfCol2onADiffTable  
3 | Col3 | ValueOfCol3onADiffTable  
4 | Col4 | ValueOfCol4onADiffTable

I'd like to use the Field_Name in a subquery to pull that value, but can't figure out how to get SQL to evaluate Field_Name as a column in the sub-query.

So something like this:

select
        Field_ID
      ,Field_Name
      ,(SELECT f.Field_Name from tblUsers u 
    where u.User_ID = @userId) as value
from 
  dbo.tblFields f

But that just returns Field_Name in the values column, not the value of it.

Do I need to put the sub-query in a separate function and evaluate that? Or some kind of dynamic SQL?

DhruvJoshi
  • 17,041
  • 6
  • 41
  • 60
Sam R.
  • 49
  • 1
  • 8

1 Answers1

2

In SQL server this would require dynamic SQL and UNPIVOT notation. see working demo

create table tblFields (Field_ID int ,Field_Name varchar(10));
insert into tblFields values
(1,'Col1')
,(2,'Col2')
,(3,'Col3')
,(4,'Col4');
declare @userId int
set @userId=1

create table tblUsers (User_ID int, col1 varchar(10),col2 varchar(10));
insert into tblUsers values
(1, 10,100),
(2,20,200);

declare @collist varchar(max)
declare @sqlquery varchar(max)

select @collist= COALESCE(@collist + ', ', '') + Field_Name
from dbo.tblFields
where exists (
       select * from sys.columns c join sys.tables t 
       on c.object_id=t.object_id and t.name='tblUsers'
       and c.name =Field_Name)
select @sqlquery=
' select Field_ID ,Field_Name, value '+
' from dbo.tblFields f  Join '+
' ( select  * from '+
'( select * '+
' from tblUsers u '+
' where u.User_ID = '+ cast(@userId as varchar(max)) +
' ) src '+
'unpivot ( Value for field in ('+ @collist+')) up )t'+
' on t.field =Field_Name'
exec(@sqlquery)
DhruvJoshi
  • 17,041
  • 6
  • 41
  • 60
  • demo works as expected, but one wrinkle... I simplified my example. The real world version needs to look in two different tables for 'value' tblUsers and tblusers_program_history_detail. I can join them but then sql complains because it has two instances of user_id on it – Sam R. Oct 10 '17 at 19:40
  • the dynamic sql statement I'm using: `select Field_ID ,(case when DB_Field_Name is null then Field_Name else DB_Field_Name end) as FieldName ,Field_Name ,DB_Field_Name ,Proper_Label ,FieldCategory ,FieldValue from dbo.tblBiographic_Fields f join (SELECT * from (select * from tblUsers u inner join tblusers_program_history_detail h on u.User_ID = h.User_ID where h.User_ID = 1) src unpivot (FieldValue for field in (' + @cols + ')) up )t on t.field = DB_Field_Name` – Sam R. Oct 10 '17 at 19:41
  • @SamR. you can also look into union of `tblUsers` and `tblusers_program_history_detail` for relevant columns – DhruvJoshi Oct 10 '17 at 19:43
  • You should be explicitly putting a list of columns here instead of * : `select * from tblUsers u inner join tblusers_program_history_detail` – DhruvJoshi Oct 10 '17 at 19:51
  • Don't tables have to have the same # of columns for union? – Sam R. Oct 10 '17 at 19:53
  • Dhruv - subbing in the columns variable ((select ' + @cols + ' from tblUsers u inner join tblusers_program_history_detail h) gets me further, no more syntax errors. But `The type of column "Initial" conflicts with the type of other columns specified in the UNPIVOT list.` EDIT: missed that "Initial" is the name of an actual column in tblUsers. That column is a nullable varchar(1) – Sam R. Oct 10 '17 at 19:56
  • recreatable in the demo by changing the length of col2 in table declaration `create table tblUsers (User_ID int, col1 varchar(10),col2 varchar(1));` – Sam R. Oct 10 '17 at 20:03
  • Resolved by casting the columns to varchars of the same size – Sam R. Oct 10 '17 at 20:46