SQL Query Dynamically get columns depending on mapping

Question

I am stuck on an issue. I am trying to create a select which returns information depending on the Rules Mapping table. The Rules table tells us where (TableName.ColumnName) value should come from. I have simplified the problem by just creating table variables to find a solution.

I have 3 tables set up.

1. @TableA which contains the Main_ID. First Table.
2. @TableB which contains more information for the Main_ID. Second table.
3. @Rules is the mapping table. It has headers for what the final select should have and also tells us where the the data should come from (What table and column).

The final expected result I am trying to achieve is this with the above rules is:

As you can see, I will have 3 columns in total with 3 rows (as I have 3 Main_ID). Data differs depending on the @Rules table. For example, Header C for Main_ID 3 is showing "Address C" as the rule mapping for Header C Main_ID 3 was "@TableB.Address" which means get the information from table @TableB and column Address.

Here is my code for the table set up:

DECLARE @TableA AS TABLE
(
    Main_ID INT IDENTITY(1, 1) ,
    Name VARCHAR(100)
)

INSERT INTO @TableA
VALUES ('Name A'), ('Name B'), ('Name C')

DECLARE @TableB AS TABLE
(
    Secondary_ID INT IDENTITY(1, 1) ,
    Main_ID INT ,
    Address VARCHAR(100)
)

INSERT INTO @TableB
VALUES (1, 'Address A'), (2, 'Address B'), (3, 'Address C')

DECLARE @Rules AS TABLE
(
    Rule_ID INT IDENTITY(1, 1) ,
    Main_ID INT ,
    Header_Name VARCHAR(100) ,
    Obtain_From VARCHAR(100) 
)

INSERT INTO @Rules
VALUES 
-- Main_ID 1 AND 2 set up same but third one wants data from a different place.
(1, 'Header A', '@TableA.Main_ID'), (1, 'Header B', '@TableB.Address'), (1, 'Header C', '@TableA.Name'),
(2, 'Header A', '@TableA.Main_ID'), (2, 'Header B', '@TableB.Address'), (2, 'Header C', '@TableA.Name'),
(3, 'Header A', '@TableA.Main_ID'), (3, 'Header B', '@TableA.Name'), (3, 'Header C', '@TableB.Address')

SELECT * FROM @TableA
SELECT * FROM @TableB
SELECT * FROM @Rules

/*
    Final result set should be:
    Header A        Header B        Header C
    1               Address A       Name A
    2               Address B       Name B
    3               Name C          Address C       
*/

What I have tried so far is joining the table together but there are too many rows.

Any ideas how I can achieve the final expected result?

Answer 1

There is a two way to do it.

The first way is static and easy. You can use CASE expression for your mapping like that:

SELECT
    [Header A]  = CASE r_headera.Obtain_From WHEN '@TableA.Main_ID' THEN CAST(a.Main_ID AS NVARCHAR(100)) WHEN '@TableA.Name' THEN CAST(a.Name AS NVARCHAR(100)) WHEN '@TableB.Address' THEN CAST(b.Address AS NVARCHAR(100)) END,
    [Header B]  = CASE r_headerb.Obtain_From WHEN '@TableA.Main_ID' THEN CAST(a.Main_ID AS NVARCHAR(100)) WHEN '@TableA.Name' THEN CAST(a.Name AS NVARCHAR(100)) WHEN '@TableB.Address' THEN CAST(b.Address AS NVARCHAR(100)) END,
    [Header C]  = CASE r_headerc.Obtain_From WHEN '@TableA.Main_ID' THEN CAST(a.Main_ID AS NVARCHAR(100)) WHEN '@TableA.Name' THEN CAST(a.Name AS NVARCHAR(100)) WHEN '@TableB.Address' THEN CAST(b.Address AS NVARCHAR(100)) END
FROM
    @TableA AS a
    LEFT JOIN @TableB AS b ON b.Main_ID = a.Main_ID
    LEFT JOIN @Rules AS r_headera ON r_headera.Main_ID = a.Main_ID AND r_headera.Header_Name = 'Header A'
    LEFT JOIN @Rules AS r_headerb ON r_headerb.Main_ID = a.Main_ID AND r_headerb.Header_Name = 'Header B'
    LEFT JOIN @Rules AS r_headerc ON r_headerc.Main_ID = a.Main_ID AND r_headerc.Header_Name = 'Header C'

Otherwise, you can use other structs such as Table Value Constructor, PIVOT, OUTER APPLY etc.

The result:

Header A	Header B	Header C
1	Address A	Name A
2	Address B	Name B
3	Name C	Address C

And the second way is Dynamic SQL with sp_executesql . If you need help with that, I can prepare an example for you.