question

SidC avatar image
SidC asked

How do I use a stored procedure to create a temptable and parse or normalize data in it?

Hi, I have a diagnosis table that has account number, diagnosis code, sequence and present on admission flag. I have a similar table for procedure data as well. I've been asked to parse or normalize the diagnoses and procedures into columns for export to a spreadsheet. For any given account, I need to output up to 49 diagnoses and 49 procedures as follows: Account DX1 DX2 DX3 POA1 POA2 POA3 Proc1 Proc2 Proc3 11111 111 222 333 N Y Y 01.01 02.02 03.03 22222 444 555 999 Y N N 04.04 05.05 06.06 I've researched use of the PIVOT function but it requires aggregation. I've started writing the following stored procedure, but am unsure whether it will produce the desired results. Can anyone review and advise if I'm heading in the correct direction? CREATE PROCEDURE getClaroPtDetail -- Add the parameters for the stored procedure here @StartDate varchar(15), @EndDate varchar(15) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here create table #ClaroDX (ID int IDENTITY(1,1), ENCOUNTER VARCHAR(20), PDX VARCHAR(15), SDX2 VARCHAR(10), SDX3 VARCHAR(10), SDX4 VARCHAR(10), SDX5 VARCHAR(10), SDX6 VARCHAR(10), SDX7 VARCHAR(10), SDX8 VARCHAR(10), SDX9 VARCHAR(10), SDX10 VARCHAR(10), SDX11 VARCHAR(10), SDX12 VARCHAR(10), SDX13 VARCHAR(10), SDX14 VARCHAR(10), SDX15 VARCHAR(10), SDX16 VARCHAR(10), SDX17 VARCHAR(10), SDX18 VARCHAR(10), SDX19 VARCHAR(10), SDX20 VARCHAR(10), SDX21 VARCHAR(10), SDX22 VARCHAR(10), SDX23 VARCHAR(10), SDX24 VARCHAR (10), SDX25 VARCHAR(10), SDX26 VARCHAR(10), SDX27 VARCHAR(10), SDX28 VARCHAR(10), SDX29 VARCHAR(10), SDX30 VARCHAR(10), SDX31 VARCHAR(10), SDX32 VARCHAR(10), SDX33 VARCHAR(10), SDX34 VARCHAR(10), SDX35 VARCHAR(10), SDX36 VARCHAR(10), SDX37 VARCHAR(10), SDX38 VARCHAR(10), SDX39 VARCHAR(10), SDX40 VARCHAR(10), SDX41 VARCHAR(10), SDX42 VARCHAR(10), SDX43 VARCHAR(10), SDX44 VARCHAR (10), SDX45 VARCHAR(10), SDX46 VARCHAR(10), SDX47 VARCHAR(10), SDX48 VARCHAR(10), SDX49 VARCHAR(10), SDXPOA1 VARCHAR(10), SDXPOA2 VARCHAR(10), SDXPOA3 VARCHAR(10), SDXPOA4 VARCHAR(10), SDXPOA5 VARCHAR(10), SDXPOA6 VARCHAR(10), SDXPOA7 VARCHAR(10), SDXPOA8 VARCHAR(10), SDXPOA9 VARCHAR(10), SDXPOA10 VARCHAR(10), SDXPOA11 VARCHAR(10), SDXPOA12 VARCHAR(10), SDXPOA13 VARCHAR (10), SDXPOA14 VARCHAR(10), SDXPOA15 VARCHAR(10), SDXPOA16 VARCHAR(10), SDXPOA17 VARCHAR(10), SDXPOA18 VARCHAR (10), SDXPOA19 VARCHAR(10), SDXPOA20 VARCHAR(10), SDXPOA21 VARCHAR(10), SDXPOA22 VARCHAR (10), SDXPOA23 VARCHAR (10), SDXPOA24 VARCHAR(10), SDXPOA25 VARCHAR(10), SDXPOA26 VARCHAR(10), SDXPOA27 VARCHAR(10), SDXPOA28 VARCHAR(10), SDXPOA29 VARCHAR(10), SDXPOA30 VARCHAR(10), SDXPOA31 VARCHAR(10), SDXPOA32 VARCHAR(10), SDXPOA33 VARCHAR(10), SDXPOA34 VARCHAR(10), SDXPOA35 VARCHAR(10), SDXPOA36 VARCHAR(10), SDXPOA37 VARCHAR(10), SDXPOA38 VARCHAR(10), SDXPOA39 VARCHAR(10), SDXPOA40 VARCHAR(10), SDXPOA41 VARCHAR(10), SDXPOA42 VARCHAR(10), SDXPOA43 VARCHAR(10), SDXPOA44 VARCHAR(10), SDXP0A45 VARCHAR(10), SDXPOA46 VARCHAR(10), SDXPOA47 VARCHAR(10), SDXPOA48 VARCHAR(10), SDXPOA49 VARCHAR(10) ) Insert Into #ClaroDX Values ('Encounter','Encounter') INSERT INTO #ClaroDX VALUES('PrimaryDX','PDX') Insert Into #ClaroDX Values ('SDX2','SDX2') Insert Into #ClaroDX Values ('SDX3','SDX3') Insert Into #ClaroDX Values ('SDX4','SDX4') Insert Into #ClaroDX Values ('SDX5','SDX5') Insert Into #ClaroDX Values ('SDX6','SDX6') Insert Into #ClaroDX Values ('SDX7','SDX7') Insert Into #ClaroDX Values ('SDX8','SDX8') Insert Into #ClaroDX Values ('SDX9','SDX9') Insert Into #ClaroDX Values ('SDX10','SDX10') Insert Into #ClaroDX Values ('SDX11','SDX11') Insert Into #ClaroDX Values ('SDX12','SDX12') Insert Into #ClaroDX Values ('SDX13','SDX13') Insert Into #ClaroDX Values ('SDX14','SDX14') Insert Into #ClaroDX Values ('SDX15','SDX15') Insert Into #ClaroDX Values ('SDX16','SDX16') Insert Into #ClaroDX Values ('SDX17','SDX17') Insert Into #ClaroDX Values ('SDX18','SDX18') Insert Into #ClaroDX Values ('SDX19','SDX19') Insert Into #ClaroDX Values ('SDX20','SDX20') Insert Into #ClaroDX Values ('SDX21','SDX21') Insert Into #ClaroDX Values ('SDX22','SDX22') Insert INto #ClaroDX Values ('SDX23','SDX23') Insert INto #ClaroDX VAlues ('SDX24','SDX24') Insert Into #ClaroDX Values ('SDX25','SDX25') Insert Into #ClaroDX Values ('SDX26','SDX26') Insert Into #ClaroDX Values ('SDX27','SDX27') Insert INto #ClaroDX Values ('SDX28','SDX28') Insert into #ClaroDX Values ('SDX29','SDX29') INsert Into #ClaroDX Values ('SDX30','SDX30') Insert INto #ClaroDX Values ('SDX31','SDX31') Insert Into #ClaroDX Values ('SDX32','SDX32') Insert INto #ClaroDX Values ('SDX33','SDX33') Insert into #ClaroDX Values ('SDX34','SDX34') Insert Into #ClaroDX Values ('SDX35','SDX35') Insert Into #ClaroDX Values ('SDX36','SDX36') Insert Into #ClaroDX Values ('SDX37','SDX37') Insert INto #ClaroDX Values ('SDX38','SDX38') Insert Into #ClaroDX Values ('SDX39','SDX39') Insert Into #ClaroDX Values ('SDX40','SDX40') Insert Into #ClaroDX Values ('SDX41','SDX41') Insert Into #ClaroDX Values ('SDX42','SDX42') Insert INto #ClaroDX Values ('SDX43','SDX43') Insert INto #ClaroDX Values ('SDX44','SDX44') Insert Into #ClaroDX Values ('SDX45','SDX45') Insert INto #ClaroDX Values ('SDX46','SDX46') Insert INto #ClaroDX Values ('SDX47','SDX47') Insert Into #ClaroDX Values ('SDX48','SDX48') Insert Into #ClaroDX Values ('SDX49','SDX49') Insert Into #ClaroDX Values('PDXPOA','SDXPOA1') Insert Into #ClaroDX Values ('SDXPOA2','SDXPOA2') Insert Into #ClaroDX Values ('SDXPOA3','SDXPOA3') Insert Into #ClaroDX Values ('SDXPOA4','SDXPOA4') Insert Into #ClaroDX Values ('SDXPOA5','SDXPOA5') Insert Into #ClaroDX Values ('SDXPOA6','SDXPOA6') Create Table #ClaroEnctr (FIRSTNAME VARCHAR(30), LASTNAME VARCHAR (30), MEDREC VARCHAR(15), ADMITDATE VARCHAR(15), DISCHDATE VARCHAR(15), LOS NUMERIC(18,0), DOB VARCHAR(8), AGE NUMERIC(18,0), SEX VARCHAR(5), DISP VARCHAR(5), ADMITDX VARCHAR(30), INSURANCEPLANCODE VARCHAR(10), CONTRACT VARCHAR(50), [MS-DRG - AFTER 10 01 2007] VARCHAR(50), ACTUALTOTALCHARGE NUMERIC(18,4)) Create Table #ClaroPX (PPX VARCHAR(15), PX2 VARCHAR(15), PX3 VARCHAR(15), PX4 VARCHAR(15), PX5 VARCHAR(15), PX6 VARCHAR(15), PX7 VARCHAR(15), PX8 VARCHAR(15), PX9 VARCHAR(15), PX10 VARCHAR(15), PX11 VARCHAR(15), PX12 VARCHAR(15), PX13 VARCHAR(15), PX14 VARCHAR(15), PX15 VARCHAR(15), PX16 VARCHAR(15), PX17 VARCHAR(15), PX18 VARCHAR(15), PX19 VARCHAR(15), PX20 VARCHAR(15), PX21 VARCHAR(15), PX22 VARCHAR(15), PX23 VARCHAR (15), PX24 VARCHAR(15), PX25 VARCHAR(15), PX26 VARCHAR(15), PX27 VARCHAR(15), PX28 VARCHAR(15), PX29 VARCHAR(15), PX30 VARCHAR(15), PX31 VARCHAR(15), PX32 VARCHAR(15), PX33 VARCHAR(15), PX34 VARCHAR(15), PX35 VARCHAR(15), PX36 VARCHAR(15), PX37 VARCHAR(15), PX38 VARCHAR(15), PX39 VARCHAR(15), PX40 VARCHAR(15)) END GO UPDATE: Using Oleg's answer, I'm trying to adapt his sample code to our database schema: with diagnosis as ( select * from ( Select [ENCOUNTER NUMBER], [SECONDARY DIAGNOSIS], [SECONDARY DIAG SEQUENCE] FROM EncounterDiag ) src pivot ( max([SECONDARY DIAG SEQUENCE]) FOR [SECONDARY DIAGNOSIS] IN ([DX1],[DX2],[DX3],[DX4],[DX5],[DX6],[DX7],[DX8],[DX9],[DX10])) pt ), POA as ( select Encounter, POA1, POA2, POA3, POA4, POA5, POA6, POA7, POA8, POA9, POA10 from ( select [ENCOUNTER NUMBER], [SECONDARY DIAGNOSIS], [PRESENT ON ADMISSION FLAG] FROM EncounterDiag ) src pivot ( max([PRESENT ON ADMISSION FLAG]) FOR [SECONDARY DIAGNOSIS] IN ([DX1], [DX2], [DX3], [DX4], [DX5], [DX6], [DX7], [DX8], [DX9], [DX10]) ) pt ), procs as ( select [ENCOUNTER NUMBER], [PROCEDURE CODE (ENCTR)], [ENCOUNTER PROC SEQUENCE] from EncounterProc src pivot ( max ([PROCEDURE CODE (ENCTR)]) FOR ProcCode in ( [Proc1], [Proc2], [Proc3], [Proc4]) ) pt ) select diagnosis.*, POA.POA1, POA.POA2, POA.POA3, POA.POA4, POA.POA5, POA.POA6, POA.POA7, POA.POA8, POA.POA9, POA.POA10, procs.Proc1, procs.Proc2, procs.Proc3, procs.Proc4 from diagnosis inner join POA on diagnosis.[ENCOUNTER NUMBER]=POA.[ENCOUNTER NUMBER] INNER JOIN procs on diagnosis.[ENCOUNTER NUMBER]=PROCS.[ENCOUNTER NUMBER] INNER JOIN ENCOUNTER ON diagnosis.[ENCOUNTER NUMBER]=ENCOUNTER.[ENCOUNTERNUMBER]; However, I receive parse errors in the POA as block of code relative to Encounter, POA1, etc. I'm unsure how to remedy this. Can anyone help?
sql-server-2005stored-procedurestemporary-table
5 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Can you provide some made up source data and desired results (possibly 3 column's worth not 49!). `PIVOT` might be suitable. Difficult to see from what you posted. Often with `PIVOT` it is just arbitrary whether you choose `MIN` or `MAX` if there is guaranteed to be at most one record being pivoted.
1 Like 1 ·
I reposted the sample data using a code block instead. Thanks for any help/guidance.
0 Likes 0 ·
@SidC The errors you are getting are simply caused by the invalid column names. The true names of the columns of the POA CTE are different from what you specify, so inside of the POA you should replace the line reading
select 
    Encounter, POA1, POA2, POA3, POA4, POA5, 
    POA6, POA7, POA8, POA9, POA10
with this one:
select 
    [ENCOUNTER  NUMBER], [DX1] POA1, [DX2] POA2, [DX3] POA3, 
    [DX4] POA4, [DX5] POA5, [DX6] POA6, [DX7] POA7, [DX8] POA8, 
    [DX9] POA9, [DX10] POA10
0 Likes 0 ·
@Oleg Thanks for this syntax correction. Can you tell I'm new with CTEs?:) The query parses now but does not return any data besides the Encounter Number, dx, poa and proc fields are all null.
0 Likes 0 ·
@SidC Yes, they are, but this is only because your actual values in the [SECONDARY DIAGNOSIS] column are't actually equal to DX1, DX2 etc. What I mean is that you have to tweak the actual query to make it work. I don't know the actual values you have, but let's call one of them **The Value 1**. This will then translate in your first CTE's select statement that you will have to use select [The Value 1] as [DX1] maning replace the occurence of [DX1] in the IN part of the pivot with [The Value 1] etc.
0 Likes 0 ·

1 Answer

· Write an Answer
Oleg avatar image
Oleg answered
You can use PIVOT though in the situations when you need to crosstab more than one column the good ol' case statement still seems to be a better choice. This is simply because the PIVOT expects only one aggregated value for the list you specify which means that you need to query the same Diagnosis table twice, first time picking the Sequence and the second time - POA as the subject for aggregation. Here is some sample data I used (you can use your own tables instead after tweaking the column names in the final script to match yours): use AdventureWorks; go -- create sample Diagnosis table create table dbo.Diagnosis ( AccountNumber int not null, DiagnosisCode varchar(10) not null, Sequence int not null, POA char(1) not null ); go -- create sample Procedures table create table dbo.Procs ( AccountNumber int not null, ProcCode varchar(10) not null, ProcValue decimal(10, 2) not null ); go -- insert some sample data insert into dbo.Diagnosis select 11111, 'DX1', 111, 'N' union all select 11111, 'DX2', 222, 'Y' union all select 11111, 'DX3', 333, 'Y' union all select 11111, 'DX4', 444, 'N' union all select 22222, 'DX1', 555, 'Y' union all select 22222, 'DX2', 777, 'N' union all select 22222, 'DX3', 888, 'N' union all select 22222, 'DX4', 999, 'Y'; go insert into dbo.Procs select 11111, 'Proc1', 1.01 union all select 11111, 'Proc2', 2.02 union all select 11111, 'Proc3', 3.03 union all select 22222, 'Proc1', 5.05 union all select 22222, 'Proc2', 7.07 union all select 22222, 'Proc3', 8.08; go Since in reality you have more columns in the final pivoted result, you can either code all the possible diagnostic codes and proc codes into your select statements or use the dynamic pivot which is pretty easy to do. Here is the solution using pivot: with diagnosis as ( select * from ( select AccountNumber, DiagnosisCode, Sequence from dbo.Diagnosis ) src pivot ( max(Sequence) for DiagnosisCode in ( [DX1], [DX2], [DX3], [DX4]) ) pt ), POA as ( select AccountNumber, DX1 POA1, DX2 POA2, DX3 POA3, DX4 POA4 from ( select AccountNumber, DiagnosisCode, POA from dbo.Diagnosis ) src pivot ( max(POA) for DiagnosisCode in ( [DX1], [DX2], [DX3], [DX4]) ) pt ), procs as ( select * from dbo.Procs src pivot ( max(ProcValue) for ProcCode in ( [Proc1], [Proc2], [Proc3], [Proc4]) ) pt ) select diagnosis.*, POA.POA1, POA.POA2, POA.POA3, POA.POA4, procs.Proc1, procs.Proc2, procs.Proc3, procs.Proc4 from diagnosis inner join POA on diagnosis.AccountNumber = POA.AccountNumber inner join procs on diagnosis.AccountNumber = procs.AccountNumber; The above shows results like this: Account DX1 DX2 DX3 DX4 POA1 POA2 POA3 POA4 Proc1 Proc2 Proc3 Proc4 ------- ----- ---- ---- ---- ---- ---- ---- ---- ---- ----- ----- ----- 11111 111 222 333 444 N Y Y N 1.01 2.02 3.03 NULL 22222 555 777 888 999 Y N N Y 5.05 7.07 8.08 9.09 The other approach would be to make this query dynamic, so you will not have to hard code the diagnosis and procedure values. Please check this questions, they have different methods of how to achieve dynamic pivoting: [Multiple counts with multiple tables][1] [please help to solve this SQL Server problem][2] Oleg [1]: http://ask.sqlservercentral.com/questions/75880/multiple-counts-with-multiple-tables [2]: http://ask.sqlservercentral.com/questions/75685/please-help-to-solve-this-sql-server-problem
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@Oleg - Thanks for your suggestion. Unfortunately, there are no values to aggregate in either the procedure or diagnosis table. If I were to use Max(Sequence) or Max(POA), wouldn't that just give me the max sequence number or max POA value?
0 Likes 0 ·
@SidC No, it would simply return the data exactly as you need it. The aggregation in the PIVOT I used is actually a fake, it is used only because the syntax requires it. In other words, when you have a record displaying, say, ID and some value and there is only one such record per "ID" but you have a statement which requires you to group by that ID then you have no choice but use some sort of aggregate on that value and regarless whether you use MIN or MAX you still get the same result. Please run the creation of sample data part, it probably will generate a small sample of data kinda resembling what you have. Then run the final select to see that the data it returns actually matches what you need. Please let me know if my assumptions are incorrect.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.