I have as selection of Columns called Parameter(1 to 10) and I need the row select to join on the column of another table. This is a 1-1 mapping as it should be previously stated to select a row.

Matrix 1 - Name, Parameter1 = First Name, Parameter2 = Last Name

Matrix 2 - Location, Parameter1 = City, Parameter2 = State, Parameter3 = Country

All the data a held in the table client detail table. The base table is over 50 columns long and there are over 100 different parameters, along with 60 matrix.

Example - Base Table

Is there a way to join these?

I have tried to join with select in the JOIN but this is not working.

CodePudding user response：

I believe you can do what you want using dynamic SQL that builds up the query and the variable conditions into a SQL string and then executes the query using sp_ExecuteSql.

Something like:

DECLARE @ConditionTemplate VARCHAR(MAX) = 'B.<ColumnName> = @Parameter<ParameterId>' 
DECLARE @ConditionSeparator VARCHAR(50) = '
    AND ' 

DECLARE @Conditions NVARCHAR(MAX) = (
    SELECT STRING_AGG(REPLACE(REPLACE(
            @ConditionTemplate
            , '<ColumnName>', QUOTENAME(P.ColumnName))
            , '<ParameterId>', CAST(P.ParameterId AS VARCHAR))
        , @ConditionSeparator)
    FROM Matrix M
    CROSS APPLY (
        VALUES
            (1, M.Parameter1),
            (2, M.Parameter2),
            (3, M.Parameter3)
    ) P(ParameterId, ColumnName)
    WHERE M.MatrixId = @MatrixId
    AND P.ColumnName > ''
)

DECLARE @Sql NVARCHAR(MAX) = '
    SELECT *
    FROM BaseTable B
    WHERE '
      @Conditions
DECLARE @Params NVARCHAR(MAX) = N'@Parameter1 VARCHAR(100), @Parameter2 VARCHAR(100), @Parameter3 VARCHAR(100)'

PRINT @Sql
EXEC sp_ExecuteSql @Sql, @Params, @Parameter1, @Parameter2, @Parameter3

Most of the work is done in the @Conditions calculation. That calculation selects the proper matrix row, flatens out the data by mapping each parameter column into a numbered row, formats each into a "column = @Parameter" equality comparison, and then uses STRING_AGG() to join the conditions together. That list of conditions is then combined with the rest of the query and executed. Because the executed dynamic SQL cannot access the parameters from the calling sql, the parameters must be explicitly passed in via the sp_ExecuteSql call.

Given the following parameters:

DECLARE @MatrixId INT = 2
DECLARE @Parameter1 VARCHAR(100) = 'Steven'
DECLARE @Parameter2 VARCHAR(100) = 'Johnson'
DECLARE @Parameter3 VARCHAR(100) = NULL

The generated SQL would be as follows:

    SELECT *
    FROM BaseTable B
    WHERE B.[First Name] = @Parameter1
    AND B.[Last Name] = @Parameter2

Which will yield the following result:

See this db<>fiddle.