I have 2.3 million rows of data that I need to pivot into about 90 columns for roughly 28k distinct rows. My main issues is my starting point is a view and not a standard table unfortunately I cant change the source of the data. Below I am attempting to use a pivot with a loop but doing one record at a time will take too long I am looking in to trying to process 10-100 at a time. I am looking to see if someone knows a better way to do this or some modifications I can make to the below code.
Example:
no field_key field_name field_type mask answer
3604 372 DOES PHYSICIAN PRACTICE OUT of MULTIPLE LOCATIONS? Yes/No No
3604 373 ARE PAs HANDLED CENTRALLY? Yes/No No
3604 374 ELECTRONIC PLATFORM UTILIZED MC, Multiple Answers APPROVE RX
3604 374 ELECTRONIC PLATFORM UTILIZED MC, Multiple Answers COVER MY MEDS
3604 375 IF OTHER, PLEASE LIST Short Answer
3604 443 OPT OUT DATE: Date/Time
3604 444 PA TEAM INITIAL REACH OUT COMPLETE: Yes/No No
Result
Doctor Number Does Physician Practice Out Of Multiple Locations? Are Pas Handled Centrally? Electronic Platform Utilized If Other, Please List Opt Out Date Pa Team Initial Reach Out Complete
3604 No No APPROVE RX; COVER MY MEDS No
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
DECLARE
@SN INT = 1
IF OBJECT_ID('tempdb.dbo.#DocID') IS NOT NULL
DROP TABLE #DocID
SELECT
D1.[NO]
, RANK() OVER ( ORDER BY D1.[NO] ASC) AS UN
INTO #DocID
FROM
(
SELECT
DISTINCT
D.[NO]
FROM [CPRSQL].[dbo].[Dyn_Doctors] AS D
) AS D1
WHILE @SN <= (SELECT MAX(DI.UN) FROM #DocID AS DI WITH (NOLOCK))
BEGIN
INSERT INTO [Finance].[dbo].[Information_Prescriber Preferences]
SELECT
DISTINCT
[Doctor Number]
, [Does Physician Practice Out Of Multiple Locations?]
, [Are Pas Handled Centrally?]
, [Electronic Platform Utilized]
, [If Other, Please List]
, [Opt Out Date]
, [Pa Team Initial Reach Out Complete]
, [Pa Team Reach Out Completed]
, CASE WHEN [Enrollment Date] = '' THEN NULL ELSE CAST([Enrollment Date] AS DATE) END AS [Enrollment Date]
, [Do Not Send Referral Confirmation Faxes]
, [Notes About Handling Pa'S]
, [Refill Request Contact Method]
, [Refill Request Notes]
, [Spoc Account]
, [Spoc Assigned]
, [Prior Auth Contact Name]
, [Preferred Method Of Contact (Prescriber Preferences)]
, [Enrolled In Enhanced Pa Program]
, [340b Account]
, [Uses Cover My Meds]
, [Important Notes]
, [Back Up Spoc]
, CASE WHEN [Date Updated] = '' THEN NULL ELSE CAST([Date Updated] AS DATE) END AS [Date Updated]
, [Updated By]
, [Does Prescriber Handle Their Own Funding?]
, [Refill Request Contact Name]
, [Refill Request Contact #]
, [Prescriber Contact Name 1 (Prescriber Preferences)]
, [Prescriber Contact Phone# (Prescriber Preferences)]
, [Celgene Refill Requests]
, [Iv Refill Requests]
, [Prior Auth Contact #]
, [Prior Auth Contact Ext]
, [Refill Request Contact Ext]
, [Preferred Method Of Contact (Prior Auth)]
, [Prior Auth Contact Email]
, [Refill Request Contact Email]
, [Prescriber Contact Email (Prescriber Preferences)]
, [Do Not Contact Prescriber For Any Clinical Clarification]
, [Advocate Contact]
, [Advocate Contact Phone #]
, [Advocate Contact Ext]
, [Advocate Contact Email]
, [Notes About Coordinating Funding]
, [Role At Office]
, [Prescriber Contact Name (Prescriber Preferences)]
, [Prescriber Contact Ext #]
, [Do Not Contact Prescriber For Venclexta Clinical Information]
, [Do Not Send Venclexta Referral Confirmation]
, [Prescriber Refuses Pa Follow Up]
, [Psd Pilot Prescriber]
, [Business Hours]
, [Communication Distribution Notes]
, [Send All First Shipments To Mdo - If Yes, Set Up Delivery Ticket.]
, [Do Not Send Xpovio Referral Confirmation]
, [Celgene Auth Requests]
, [Shipping Instructions]
, [How Many Days Prior Should The Team Follow Up For Refill?]
, [Best Time To Call]
, [Rd Assigned]
, [Back Up Rd Assigned]
, [Regional Director (Field)]
, [Field Sales Director]
, [Alternate Location Notes]
, [Alternate Location]
, [Days At Alternate Location]
, [Alternate Location Fax#]
, [Alternate Location Ph#]
, [Alternative Confirmation Fax#]
, [Alternative Confirmation Email]
, [Onboarding Preferred Method Of Contact]
, [Onboarding Prescriber Contact Name]
, [Onboarding Prescriber Contact Email]
, [Onboarding Prescriber Contact Phone#]
, [Onboarding Prescriber Contact Ext#]
, [Rph Preferred Method Of Contact]
, [Prescriber Contact Phone# (Rx Set Up/Rph/Clinical Clarification)]
, [Prescriber Contact Email (Rx Set Up/Rph/Clinical Clarification)]
, [Prescriber Contact Name (Rx Set Up/Rph/Clinical Clarification)]
, [Prescriber Contact Ext#]
, [If Pa Team Can Contact Prescriber, How Does Prescriber Want To Receive Cmm Key?]
, [Preferred Method Of Contact (Outbound Scheduling)]
, [If Prescriber Handles Their Own Funding, Please Clarify Specifically What They Prefer To Handle]
, [Only Rd Can Communicate Prior Auth Information]
, [Prior Auth Contact Fax#]
, [Send Fax Confirmation Via Fax & Email]
, [Does Prescriber Want To Be Notified Prior To Transfer?]
, [Does Prescriber Have Specific Contacts At Other Sp'S] AS [Does Prescriber Have Specific Contacts At Other Sp's]
, [Preferred Method Of Contact (Transfers)]
, [Transfer Notes]
, [Prescriber Wants Notified Prior To Contacting Patient If Patient Copay Is Over]
, [Advocates Preferred Method Of Contact]
, [Notes About Clinical Clarifications]
, [Notes About Fax Confirmation]
, GETDATE() AS [LoadDate]
FROM
(
SELECT
DISTINCT
D2.[Doctor Number]
, D2.[field_name]
, COALESCE(D2.[Combined Answers], D2.[answer]) AS [Answer]
FROM
(
SELECT
DISTINCT
[no] AS [Doctor Number]
,[field_key]
, [Finance].[dbo].[ConvertFirstLetterinCapital](REPLACE(CASE
WHEN [field_key] = 792 THEN 'Preferred Method of Contact (Prescriber Preferences):'
WHEN [field_key] = 894 THEN 'Prescriber Contact Name 1 (Prescriber Preferences):'
WHEN [field_key] = 895 THEN 'Prescriber Contact Phone# (Prescriber Preferences):'
WHEN [field_key] = 911 THEN 'Prescriber Contact Email (Prescriber Preferences):'
WHEN [field_key] = 923 THEN 'Prescriber Contact Name (Prescriber Preferences):'
WHEN [field_key] = 2262 THEN 'Prescriber Contact Phone# (Rx Set Up/RPH/Clinical Clarification):'
WHEN [field_key] = 2263 THEN 'Prescriber Contact Email (Rx Set Up/RPH/Clinical Clarification):'
WHEN [field_key] = 2264 THEN 'Prescriber Contact Name (Rx Set Up/RPH/Clinical Clarification):'
WHEN [field_key] = 2331 THEN 'Preferred Method of Contact (Transfers):'
WHEN [field_key] = 908 THEN 'Preferred Method of Contact (Prior Auth):'
WHEN [field_key] = 2269 THEN 'Preferred Method of Contact (Outbound Scheduling):'
ELSE [field_name]
END,':','')) AS [field_name]
,[field_type]
,[mask]
, [answer]
, LTRIM(STUFF(
(SELECT
'; ' + D1.[answer]
FROM [CPRSQL].[dbo].[Dyn_Doctors] AS D1 WITH (NOLOCK)
WHERE D1.[field_type] = 'MC, Multiple Answers'
AND D1.[no] = D.[no]
AND D1.[field_key] = D.[field_key]
FOR XML PATH('')), 1, 1, '')) AS [Combined Answers]
FROM [CPRSQL].[dbo].[Dyn_Doctors] AS D
WHERE [NO] =
(
SELECT
DI.[no]
FROM #DocID AS DI WITH(NOLOCK)
WHERE DI.UN = @SN
)
) AS D2
) AS Base
PIVOT
(
MAX(answer)
FOR field_name IN
(
[Does Physician Practice Out Of Multiple Locations?]
, [Are Pas Handled Centrally?]
, [Electronic Platform Utilized]
, [If Other, Please List]
, [Opt Out Date]
, [Pa Team Initial Reach Out Complete]
, [Pa Team Reach Out Completed]
, [Enrollment Date]
, [Do Not Send Referral Confirmation Faxes]
, [Notes About Handling Pa'S]
, [Refill Request Contact Method]
, [Refill Request Notes]
, [Spoc Account]
, [Spoc Assigned]
, [Prior Auth Contact Name]
, [Preferred Method Of Contact (Prescriber Preferences)]
, [Enrolled In Enhanced Pa Program]
, [340b Account]
, [Uses Cover My Meds]
, [Important Notes]
, [Back Up Spoc]
, [Date Updated]
, [Updated By]
, [Does Prescriber Handle Their Own Funding?]
, [Refill Request Contact Name]
, [Refill Request Contact #]
, [Prescriber Contact Name 1 (Prescriber Preferences)]
, [Prescriber Contact Phone# (Prescriber Preferences)]
, [Celgene Refill Requests]
, [Iv Refill Requests]
, [Prior Auth Contact #]
, [Prior Auth Contact Ext]
, [Refill Request Contact Ext]
, [Preferred Method Of Contact (Prior Auth)]
, [Prior Auth Contact Email]
, [Refill Request Contact Email]
, [Prescriber Contact Email (Prescriber Preferences)]
, [Do Not Contact Prescriber For Any Clinical Clarification]
, [Advocate Contact]
, [Advocate Contact Phone #]
, [Advocate Contact Ext]
, [Advocate Contact Email]
, [Notes About Coordinating Funding]
, [Role At Office]
, [Prescriber Contact Name (Prescriber Preferences)]
, [Prescriber Contact Ext #]
, [Do Not Contact Prescriber For Venclexta Clinical Information]
, [Do Not Send Venclexta Referral Confirmation]
, [Prescriber Refuses Pa Follow Up]
, [Psd Pilot Prescriber]
, [Business Hours]
, [Communication Distribution Notes]
, [Send All First Shipments To Mdo - If Yes, Set Up Delivery Ticket.]
, [Do Not Send Xpovio Referral Confirmation]
, [Celgene Auth Requests]
, [Shipping Instructions]
, [How Many Days Prior Should The Team Follow Up For Refill?]
, [Best Time To Call]
, [Rd Assigned]
, [Back Up Rd Assigned]
, [Regional Director (Field)]
, [Field Sales Director]
, [Alternate Location Notes]
, [Alternate Location]
, [Days At Alternate Location]
, [Alternate Location Fax#]
, [Alternate Location Ph#]
, [Alternative Confirmation Fax#]
, [Alternative Confirmation Email]
, [Onboarding Preferred Method Of Contact]
, [Onboarding Prescriber Contact Name]
, [Onboarding Prescriber Contact Email]
, [Onboarding Prescriber Contact Phone#]
, [Onboarding Prescriber Contact Ext#]
, [Rph Preferred Method Of Contact]
, [Prescriber Contact Phone# (Rx Set Up/Rph/Clinical Clarification)]
, [Prescriber Contact Email (Rx Set Up/Rph/Clinical Clarification)]
, [Prescriber Contact Name (Rx Set Up/Rph/Clinical Clarification)]
, [Prescriber Contact Ext#]
, [If Pa Team Can Contact Prescriber, How Does Prescriber Want To Receive Cmm Key?]
, [Preferred Method Of Contact (Outbound Scheduling)]
, [If Prescriber Handles Their Own Funding, Please Clarify Specifically What They Prefer To Handle]
, [Only Rd Can Communicate Prior Auth Information]
, [Prior Auth Contact Fax#]
, [Send Fax Confirmation Via Fax & Email]
, [Does Prescriber Want To Be Notified Prior To Transfer?]
, [Does Prescriber Have Specific Contacts At Other Sp'S]
, [Preferred Method Of Contact (Transfers)]
, [Transfer Notes]
, [Prescriber Wants Notified Prior To Contacting Patient If Patient Copay Is Over]
, [Advocates Preferred Method Of Contact]
, [Notes About Clinical Clarifications]
, [Notes About Fax Confirmation]
)
) AS PT
OPTION (RECOMPILE)
SET @SN = @SN + 1
END
What I have tried:
I have tried pivoting, dynamic SQL, using a loop
Submit an article or tip