Dynamically using variables as column name in join

Question

DECLARE @PatientID_VAR Varchar(50) SELECT @PatientID_VAR = COLUMN_NAME from Pareto_Dashboard.dbo.TEST_TABLE_COLUMNS where ID = 1


Select 
@Branch_Code_VAR,
@Branch_Name_VAR,
Year, 
Month,
COUNT(Distinct @PatientID_VAR)as Total_Patients,
COUNT(@PatientID_VAR) as Total_Visit,
count(@Test_Code_VAR) as Total_Test

from (
    -----------Taking required columns like Pat_ID,Branch_code & Name,Visit_date,Test_code,Name & Cost
        
        select distinct  t2.[@PatientID_var] ,Branch_Code,Branch_Name,t2.Visit_Date,Test_Code,Test_Cost
        ,Year(t2.Visit_Date)as Year,MONTH(t2.Visit_Date)as Month
        from 
            Maternity_DB.dbo.Visit_table as t2
            left join
            Maternity_DB.dbo.Test_table as t3
            on t2.PatientID=t3.PatientID    
            )s
    Group By Branch_Code,Branch_Name,Year,Month
    order by Branch_Code

Problem: t2.[@PatientID_var] throws a syntax error saying:

invalid column name

Kindly let me know how to use columns name dynamically in a join query.

Answer 1

You can't really do that this way. What you can do is write dynamic SQL which will be messy, or use a case statement. I don't know what kind of columns you may have, so I'm going to assume it's PatientID or PatientName that you want to be able to select, both of them available in your Visit_table. You could then hardcode something like this:

CASE
  WHEN @PatientID_VAR = 'Pat_ID'
  THEN t2.Pat_ID
  WHEN @PatientID_VAR = 'Patient_Name'
  THEN t2.Patient_Name
  -- you can cover as many as you want here
END AS Selected_PatientID_VAR

Then your resulting code would be:

DECLARE @PatientID_VAR Varchar(50) SELECT @PatientID_VAR = COLUMN_NAME from Pareto_Dashboard.dbo.TEST_TABLE_COLUMNS where ID = 1


Select 
@Branch_Code_VAR,
@Branch_Name_VAR,
Year, 
Month,
COUNT(Distinct Selected_PatientID_VAR)as Total_Patients,
COUNT(Selected_PatientID_VAR) as Total_Visit,
count(@Test_Code_VAR) as Total_Test

from (
    -----------Taking required columns like Pat_ID,Branch_code & Name,Visit_date,Test_code,Name & Cost
        
        select distinct  CASE
                           WHEN @PatientID_VAR = 'Pat_ID'
                           THEN t2.Pat_ID

                           WHEN @PatientID_VAR = 'Patient_Name'
                           THEN t2.Patient_Name

                           -- you can cover as many as you want here
                         END AS Selected_PatientID_VAR
         ,Branch_Code,Branch_Name,t2.Visit_Date,Test_Code,Test_Cost
        ,Year(t2.Visit_Date)as Year,MONTH(t2.Visit_Date)as Month
        from 
            Maternity_DB.dbo.Visit_table as t2
            left join
            Maternity_DB.dbo.Test_table as t3
            on t2.PatientID=t3.PatientID    
            )s
    Group By Branch_Code,Branch_Name,Year,Month
    order by Branch_Code