x
login about faq Site discussion (meta-askssc)

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

0

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?

more ▼

asked Jul 21 '11 at 09:27 AM in Default

SidC gravatar image

SidC
23 ● 2 ● 2 ● 3

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.

Jul 21 '11 at 09:30 AM Martin 1

I reposted the sample data using a code block instead. Thanks for any help/guidance.

Jul 21 '11 at 10:49 AM SidC

@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
Jul 22 '11 at 11:34 AM Oleg

@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.

Jul 22 '11 at 02:23 PM SidC

@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.

Jul 23 '11 at 05:20 PM Oleg
(comments are locked)
10|1200 characters needed characters left
▼ Viewable by all users
1 answer: sort oldest ▼
0

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

please help to solve this SQL Server problem

Oleg

more ▼

answered Jul 21 '11 at 12:54 PM

Oleg gravatar image

Oleg
15.4k ● 1 ● 4 ● 24

@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?

Jul 21 '11 at 02:05 PM SidC

@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.

Jul 21 '11 at 02:22 PM Oleg
(comments are locked)
10|1200 characters needed characters left
▼ Viewable by all users
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments



Facebook logo Follow Ask SSC on Facebook
Find Ask SSC on Google+
linkedin logo Find us on LinkedIn

Topics:

x1843
x343
x30

asked: Jul 21 '11 at 09:27 AM

Seen: 991 times

Last Updated: Jul 22 '11 at 10:50 AM

Related Questions

Need to Create function for this block

T-SQL: Stored proc with temporary table

Split a field into rows and columns - SQL 2005

how to get the value used in SP or Views or Functions or SQL Jobs?

Dropping temp tables before ending Sproc

How to do stored procedure?

What is the use of #?

Update Else Insert Procedure

Sql server Stored procedure to execute the below query

Looping through a dataset of records.

Copyright © 2002-2012 Simple Talk Publishing. All Rights Reserved. If you have any queries, please contact the site administrators.
Ask SQL Server Central is a community service provided by Red Gate.

AnswerHubEnterprise
Social Q&A