Is it possible to map row value of one table to column header of another table without dynamic query?

Question

I have one table with column headers in rows and another table with those values as column names. Every time i need to run dynamic query with pivot to get result. Can you please help?

below is the table structure:

CREATE TABLE Temp_Columns (Project_Id INT, Temp_Column NVARCHAR(100))

INSERT INTO Temp_Columns Values(1, 'Temp2')
INSERT INTO Temp_Columns Values(2, 'Temp1')
INSERT INTO Temp_Columns Values(3, 'Temp2')
INSERT INTO Temp_Columns Values(4, 'Temp4')

CREATE TABLE Tracking_Data(Track_Id INt PRIMARY KEY IDENTITY(1,1), Project_Id INT, Temp1 NVARCHAR(MAX), Temp2 NVARCHAR(MAX), Temp3 NVARCHAR(MAX), Temp4 NVARCHAR(MAX))

INSERT INTO Tracking_Data VALUES (1, 'Order_1','01-Jan-2021','P_Address 1','City 1')
INSERT INTO Tracking_Data VALUES (2, '02-Jan-2021','Order_2','P_Address 2','City 21')
INSERT INTO Tracking_Data VALUES (1, 'Order_4','06-Jan-2021','P_Address 3','City 313')
INSERT INTO Tracking_Data VALUES (2, '14-Jan-2021','Order_5','P_Address 45','City 14')
INSERT INTO Tracking_Data VALUES (1, 'Order_6','04-Jan-2021','P_Address 45','City 14')
INSERT INTO Tracking_Data VALUES (3, 'Order_7','03-Jan-2021','P_Address 15','City 145')

Now what i need is to map Temp_Column rows with Trakcing_Data columns to get date wise result from Tracking_Data

Answer 1

Is the following result what you want:

    ;with cte as(select 
    max(CASE  WHEN Temp_Column='Temp2' THEN 1 ELSE 0 end) Temp2,
    max(CASE  WHEN Temp_Column='Temp1' THEN 2 ELSE 0 end) Temp1,
    max(CASE  WHEN Temp_Column='Temp2' THEN 3 ELSE 0 end) Temp22,
    max(CASE  WHEN Temp_Column='Temp4' THEN 4 ELSE 0 end) Temp4
    FROM Temp_Columns)

    select *
    from Tracking_Data D 
    Left join cte c on c.Temp2=D.Project_Id or c.Temp1=D.Project_Id
    or c.Temp4=D.Project_Id or c.Temp22=D.Project_Id

If what you want is the value in Temp_Column instead of * in

select * from Tracking_Data

as far as I know, you must use dynamic sql, static sql cannot be achieved.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
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 2

Please also remember to accept the answers if they helped.
Your action would be helpful to other users who encounter the same issue and read this thread.

Answer 3

NSERT INTO Tracking_Data VALUES (1, 'Order_1','01-Jan-2021','P_Address 1','City 1')
INSERT INTO Tracking_Data VALUES (2, '02-Jan-2021','Order_2','P_Address 2','City 21')
INSERT INTO Tracking_Data VALUES (1, 'Order_4','06-Jan-2021','P_Address 3','City 313')
INSERT INTO Tracking_Data VALUES (2, '14-Jan-2021','Order_5','P_Address 45','City 14')
INSERT INTO Tracking_Data VALUES (1, 'Order_6','04-Jan-2021','P_Address 45','City 14')
INSERT INTO Tracking_Data VALUES (3, 'Order_7','03-Jan-2021','P_Address 15','City 145')