November 29, 2019 at 4:28 pm
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)
November 29, 2019 at 4:34 pm
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
November 29, 2019 at 5:13 pm
You should be using sp_executesql
instead of using EXEC
. EXEC has been deprecated.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 2, 2019 at 4:35 pm
drew.allen wrote:EXEC has been deprecated.
Drew
Have you got a Microsoft link that states this?
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
December 2, 2019 at 4:45 pm
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy