I need to use the rows of one column to select another column

Question

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, depending on the Matrix selected. This needs to be driven by the matrix selected. The rows to columns heading is a 1-1 mapping, but the values are 1 to many. Like below:

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 possibilities.

Example - Base Table

First Name	Last Name	City	State	Country
Mary	Smith	Austin	Texas	USA
Steven	Johnson	Toronto	Ontario	Canada

Matrix	Parameter1	Parameter2	Parameter3
1	City	State	Country
2	First Name	Last Name

I need the outputs to be like :

Output A When Matrix 1 is selected

City	State	Country
Austin	Texas	USA
Toronto	Ontario	Canada

Output B When Matrix 2 selected

First Name	Last Name
Mary	Smith
Steven	Johnson

What is "Azure T-SQL"? Do you mean an Azure Managed Instance? Azure Synapse? Azure SQL Edge? Azure *something else*? — Thom A, Jan 04 '23 at 15:37
I use SSMS but the server is Azure Synapse. Unfortunately, the set up does not allow to use the normal Azure Synapse GUI. — dszakris, Jan 04 '23 at 15:39
Please read [this](https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) for some tips on improving your question. A complete example with input data and expected results and an explanation of how to get from one to the other will help us help you. It isn't quite clear which tables you are trying to join or to what purpose. — HABO, Jan 04 '23 at 15:41
@HABO Thanks, I work under a data protection act where I'm not allowed to share these details. I'm guessing that I will not able able to ever ask question. We are in the process of moving from MUMPS to T-SQL in the Azure Synapse environment. But thank you for your help. — dszakris, Jan 04 '23 at 15:46

T N · Answer 1 · 2023-01-05T07:01:20.390

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:

First Name	Last Name	City	State	Country
Steven	Johnson	Toronto	Ontario	Canada

See this db<>fiddle.

This is great, unfortunately, it can only show 1 row, were as I have many. I have updated the question if that helps. I'm sorry if my original question was not clear. — dszakris, Jan 10 '23 at 12:33

Aswin · Answer 2 · 2023-02-03T15:05:26.963

I tried to repro this. Below is the SQL script.

Input Tables

1. base_table

First Name	Last Name	City	State	Country
Mary	Smith	Austin	Texas	USA
Steven	Johnson	Toronto	Ontario	Canada

2. Matrix table

Matrix	Parameter1	Parameter2	Parameter3
1	City	State	Country
2	[First Name]	[Last Name]	null

SQL script:

DECLARE @StgTable TABLE ([ColName] varchar(256), Done bit default(0));
Insert into @StgTable( [ColName] ) select concat_ws(',',parameter1,parameter2,parameter3) from matrix_table
DECLARE @SQLstmnt nvarchar(max), @colnames varchar(200);
WHILE EXISTS (SELECT 1 FROM @StgTable WHERE Done = 0) BEGIN
    SELECT TOP 1 @colnames = [ColName]  FROM @StgTable  WHERE Done = 0;
    SET @SQLstmnt = 'SELECT '+ @colnames +' FROM base_table ;';
    EXECUTE sp_executesql @SQLstmnt;
    UPDATE @StgTable SET Done = 1 WHERE [ColName] = @colnames;
END;

See this db<>fiddle

Output:

City	State	Country
Austin	Texas	USA
Toronto	Ontario	Canada

First Name	Last Name
Mary	Smith
Steven	Johnson

enter image description here

I need to use the rows of one column to select another column

2 Answers2