SQLTeam.com | Weblogs | Forums

Pivoting more than aggregate column

sql2008r2

#1

I am in the health industry and am facing a problem currently being handled (legacy code) by nested selects. In a nutshell, a patient visits a hospital and I need to generate a report including the insurance payor information. Currently, the information is being split into information by payor with subselects in the FROM clause and runs for minutes. I approached this with a pivot in another report where I only needed the insuror name like this:

--==========================================================================================
-- STEP 8: GET PAYOR RANK; order primary, secondary, tertiary and quaternary payers by column.
-- ~ 0 second || 20,590 rows
--==========================================================================================
IF OBJECT_ID('tempdb..#Pyr') IS NOT NULL
DROP TABLE #Pyr
GO

SELECT
	vst_int_id,
	ISNULL([4981], 'NONE') AS Primary_Payor,
	ISNULL([4978], 'NONE') AS Secondary_Payor,
	ISNULL([4980], 'NONE') AS Tertiary_Payor,
	ISNULL([4979], 'NONE') AS Quaternary_Payor
INTO
	#Pyr

FROM
	(SELECT 
		vst_int_id,
		plan_ds,
		pyr_seq_no
	FROM
		#Ins
		JOIN
			TPM700_PAYOR_PLAN									AS TPM700
			ON 
				TPM700.plan_int_id = #Ins.cur_pln_int_id)		AS p
	    PIVOT 
			(MAX(plan_ds)
			 FOR 
				pyr_seq_no IN 
				([4981],
				[4978],
				[4980],
				[4979]))										AS pvt
GO

What I am trying to do with this report is to include full information on the insurors with address, phone, various keys, and insured name. The output should look like this:

Insurer1_Plan_Cd	Insurer1_Plan_Desc	Insurer1_Bene_No	Insurer1_Subsc_Lst_Nm	Insurer1_Subsc_Fst_Nm	Insurer1_Group_Nm	Insurer1_Pyr_Addr	Insurer1_Phone	Insurer2_Plan_Cd	Insurer2_Plan_Desc	Insurer2_Bene_No	Insurer2_Subsc_Lst_Nm	Insurer2_Subsc_Fst_Nm	Insurer2_Group_Nm	Insurer2_Pyr_Addr	Insurer2_Phone	Insurer3_Plan_Cd	Insurer3_Plan_Desc	Insurer3_Bene_No	Insurer3_Subsc_Lst_Nm	Insurer3_Subsc_Fst_Nm	Insurer3_Group_Nm	Insurer3_Pyr_Addr	Insurer3_Phone

MOLIN MOLINA HEALTHCARE OF OHIO, INC 12345678901 RABBIT ROGER MCAIDHMO PO BOX 22712 ~ LONG BEACH~ CA~ 908015712 no phone no phone no phone
AnthemMC ANTHEM MEDICARE 23456789012 BUNNY BUGS PO BX 105187 ~ ATLANTA~ GA~ 30348-5187 no phone no phone no phone
MOLIN MOLINA HEALTHCARE OF OHIO, INC 34567890123 COTTONTAIL PETER MCAIDHMO PO BOX 22712 ~ LONG BEACH~ CA~ 908015712 no phone no phone no phone
AnthemMC ANTHEM MEDICARE 45678901234 FOOFOO LITTLE BUNNY PO BX 105187 ~ ATLANTA~ GA~ 30348-5187 no phone no phone no phone
AnthemMC ANTHEM MEDICARE 56789012345 RABBIT JACK PO BX 105187 ~ ATLANTA~ GA~ 30348-5187 no phone no phone no phone

The pivot works well for just the column containing the insuror name, but there has to be a more elegant way other than a spaghetti patch of CASE statements or SUBSELECT s. Any ideas?

(I am not sure of what the preview window is actually showing me, so I may have to edit this to make it display correctly.)