Сводные записи в SQL
У меня есть 2,3 миллиона строк данных, которые мне нужно свернуть примерно в 90 столбцов для примерно 28 тысяч различных строк. Мои основные проблемы заключаются в том, что моя отправная точка-это представление, а не стандартная таблица, к сожалению, я не могу изменить источник данных. Ниже я пытаюсь использовать pivot с циклом, но выполнение одной записи за раз займет слишком много времени, я смотрю на то, чтобы попытаться обработать 10-100 за раз. Я ищу, чтобы увидеть, если кто-то знает лучший способ сделать это или некоторые изменения, которые я могу внести в приведенный ниже код.
Пример:
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
Результат
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
/*
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
Что я уже пробовал:
Я пробовал поворачивать, динамический SQL, используя цикл
Jörgen Andersson
Я вижу здесь довольно много запахов кода, "уровень изоляции читается незафиксированным", "с (NOLOCK)" и отчетливым.
В чем причина этой петли?