Joining my created pivot table to other tables/existing query

  • Good morning,

    I've created a dynamic pivot table using data from an existing table "donor_procedure_eligibility"

    I am not trying to join this pivot table I created to a different, existing table called "donor" on the unique data column of "donor_id".  So I want to left join donor_id from the pivot table to donor_id of the donor table.  I just don't know the syntax to use or if it is possible.

    Here is my pivot query:

    DECLARE @SQLStatement NVARCHAR(MAX) = N'' --Variable to hold t-sql query

    DECLARE @UniqueProcedureCodesToPivot NVARCHAR(MAX) = N'' --Variable to hold unique customers to be used in PIVOT clause

    DECLARE @PivotColumnsToSelect NVARCHAR(MAX) = N'' --Variable to hold pivot column names with alias to be used in SELECt clause

    --Extarct unique customer names with pivot formattings

    SELECT @UniqueProcedureCodesToPivot = @UniqueProcedureCodesToPivot + ', [' + case when procedure_code = 'DR' or procedure_code = 'WB' or procedure_code = 'TR' or procedure_code = 'HH' or procedure_code = 'PO' then (COALESCE(concat (procedure_code, '_', instrument_code),'')) else (coalesce(procedure_code, '')) end + ']' FROM (SELECT DISTINCT procedure_code, instrument_code FROM donor_procedure_eligibility)DT

    SELECT @UniqueProcedureCodesToPivot = LTRIM(STUFF(@UniqueProcedureCodesToPivot, 1, 1, '')) --Remove first comma and space

    --Generate column names to be put in SELECT list with NULL handling and aliases also

    SELECT @PivotColumnsToSelect = case when procedure_code = 'DR' or procedure_code = 'WB' or procedure_code = 'TR' or procedure_code = 'HH' or procedure_code = 'PO' then (@PivotColumnsToSelect + ', ISNULL([' + COALESCE(concat (procedure_code, '_', instrument_code),'') + '], 0) AS [' + procedure_code + '_' + instrument_code + ']')

    else (@PivotColumnsToSelect + ', ISNULL([' +(COALESCE(procedure_code,'')) + '], 0) AS [' + procedure_code + '_' + ']') end

    FROM (SELECT DISTINCT procedure_code, instrument_code FROM donor_procedure_eligibility)DT

    --Generate dynamic PIVOT query here

    SET @SQLStatement =

    N'SELECT donor_id '

    + @PivotColumnsToSelect +

    '

    FROM donor_procedure_eligibility

    PIVOT

    (

    MAX(eligible_date) FOR

    procedure_code IN

    (' + @UniqueProcedureCodesToPivot + ')

    ) AS PVT

    '

    --Execute the dynamic t-sql PIVOT query below

    EXEC (@SQLStatement)

  • You could include the join to donor in your dynamic SQL, or you could dump the results of EXEC (@SQLStatement) into a temp table and join to that.

    If the value of @PivotColumnsToSelect is going to come from user input, make sure you understand and guard against SQL injection.

    John

  • You should be using sp_executesql instead of using EXEC.  EXEC has been deprecated.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • drew.allen wrote:

    EXEC has been deprecated.

    Drew

    Have you got a Microsoft link that states this?

  • Jonathan AC Roberts wrote:

    drew.allen wrote:

    EXEC has been deprecated.

    Drew

    Have you got a Microsoft link that states this?

    https://docs.microsoft.com/en-us/sql/t-sql/language-elements/execute-transact-sql?view=sql-server-ver15

     Note

    This feature is in maintenance mode and may be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

    They use different terminology, but the essence is the same.  You should not be using this in new development work.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • I agree about preferring sp_execute_sql, but I think it's only one particular feature of the command that's deprecated, not the command itself.

    John

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply