Pivoting records in SQL

Question

0.00/5 (No votes)

See more:

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

SQL

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

DECLARE 
	@SN INT = 1

/*

THE FIRST TEMP TABLE GATHERS ALL THE INDIVIDUAL [NO] AND ASSIGNS THEM A RANK, IN TOTAL THERE IS ROUGHLY 28K DISTINCT RECORDS

*/


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

-----------------------------------------------------------------------------------------------------------------------------------------

/*

THE LOOP BELOW USES THE RANK ASSIGNED ABOVE TO PULL THE SPECFIC NO NUMBER INTO THE CODE AND GET THE DATA FOR THAT PARTTICULAR RECORD. ONCE DATA IS TRANSPOSE RECORDS IS THEN ADDED TO TABLE 
AND THE LOOP KEEPS ADDING ONE AND GETTIUNG THE NEXT [NO] TILL IT GETS TO THE MAX. 

*/

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

Posted 28-Sep-20 7:42am

Member 14950697

Updated 28-Sep-20 22:10pm

Maciej Los

v2

Add a Solution

Comments

Jörgen Andersson 29-Sep-20 4:41am

I'm seeing quite some code smells here, "ISOLATION LEVEL READ UNCOMMITTED", "WITH (NOLOCK)" and Distinct.

What is the reason for the loop?

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2020-09-28T22:10:00

Sorry, but no one is able to help you without having complete structure of your database. You need to provide example data and expected result.

As to your sql code...

Too many SELECT's!
I'd suggest to use JOIN[^]'s. For further details, please see: Visual Representation of SQL Joins[^]

Too many CASE WHEN condition!
I'd suggest to replace this piece of code:

SQL

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]

with helper table. For above set of values in field_key, create (temporary) table and insert such of data. Then join them via LEFT OUTER JOIN.

At the beginning move your focus to create a query to fetch un-pivoted data in the shortest time as possible! Then start transposing them.

Good luck!

Pivoting records in SQL

1 solution

Solution 1

Add your solution here

Preview 0

Pivoting records in SQL

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us