SQL - Get dynamic column

Question

i wish to have data from dynamic column of another Table

Table A

ID	Name	TableBColmn	TableBID
1	John	TableBColumn1	101
2	Mary	TableBcolumn3	102

TableB

ID	TableBColumn1	TableBColumn2	TableBColumn3	TableBColumn4
101	Married	NULL	NULL	NULL
102	NULL	NULL	Engineer	NULL

Can i come up the result like this

Header 1	Header
John	Married
Marry	Engineer

Thanks a lot.

Answer 1

Hi @WONG Tony,

Please try the following solution.

It is fully dynamic and elegant, and not using any conditional logic.

DECLARE @TableA TABLE
(
    ID INT PRIMARY KEY,
    Name VARCHAR(20),
    TableBColmn VARCHAR(100),
    TableBID INT
);
INSERT INTO @TableA VALUES
(1, 'John', 'TableBColumn1', 101),
(2, 'Mary', 'TableBColumn3', 102);

DECLARE @TableB TABLE 
(
    ID INT PRIMARY KEY,
    TableBColumn1 VARCHAR(20),
    TableBColumn2 VARCHAR(20),
    TableBColumn3 VARCHAR(20),
    TableBColumn4 VARCHAR(20)
);
INSERT INTO @TableB VALUES
(101, 'Married', NULL, NULL, NULL),
(102, NULL, NULL, 'Engineer', NULL);

SELECT A.ID, A.Name AS Header1
	, c.value('(/root/*[local-name()=sql:column("A.TableBColmn")]/text())[1]', 'VARCHAR(20)') AS Header
	--, c
FROM @TableA as A inner join 
@TableB as B on A.TableBID = B.ID
CROSS APPLY (SELECT B.* FOR XML PATH(''), TYPE, ROOT('root')) AS t(c);

Output

ID	Header1	Header
1	John	Married
2	Mary	Engineer

Answer 2

Hi @WONG Tony

You can try this query.

create table TableA(ID int,Name varchar(20),TableBColmn varchar(100),TableBID int);
insert into TableA values(1,'John','TableBColumn1',101),
(2,'Mary','TableBcolumn3',102);
create table TableB(ID int,TableBColumn1 varchar(20),TableBColumn2 varchar(20),
TableBColumn3 varchar(20),TableBColumn4 varchar(20));
insert into TableB values(101,'Married',null,null,null),
(102,null,null,'Engineer',null);

select A.Name as Header1,
       case when A.TableBColmn = 'TableBColumn1' then B.TableBColumn1
	        when A.TableBColmn = 'TableBColumn2' then B.TableBColumn2
			when A.TableBColmn = 'TableBColumn3' then B.TableBColumn3
			when A.TableBColmn = 'TableBColumn4' then B.TableBColumn4
		end as Header
from TableA as A inner join TableB as B on A.TableBID = B.ID;

Best regards,

Percy Tang

---

If the answer is the right solution, please click "Accept Answer". If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Thanks a lot

Percy and Yitzhak