Dynamically adding columns

Question

Post reply

Dynamically adding columns

J Moore

Old Hand

Points: 398
More actions
December 15, 2011 at 2:34 pm

#252157

I'm trying to do a pull on patient records depending on the diagnosis. It has to do with a state tumor registry. I've tried to make this as short as possible and hopefully I didn't leave out pertinent information.
drop table #xicd9tempheader
GO
CREATE TABLE #xicd9tempheader
(
visit_ext_id INT NOT NULL
,icd9_int_id INT NOT NULL
);
GO
INSERT INTO #xicd9tempheader
(visit_ext_id,icd9_int_id)
select 123456,5790
UNION ALL
SELECT 234567,7556
UNION ALL
SELECT 567890,7219
UNION ALL
SELECT 123456,4213
UNION ALL
SELECT 123456,5129
UNION ALL
SELECT 234567,7689
UNION ALL
SELECT 567890,9516
UNION ALL
SELECT 234567,9899
UNION ALL
SELECT 123456,2279
GO
CREATE TABLE #xicd9tempcodes
(
icd9_int_id INT
,icd9_code varchar(10)
);
GO
INSERT INTO #xicd9tempcodes
(icd9_int_id,icd9_code)
SELECT 5790,'1533'
UNION ALL
SELECT 7556,'4019'
UNION ALL
SELECT 7219,'2768'
UNION ALL
SELECT 4213,'E8497'
UNION ALL
SELECT 5129,'V1259'
UNION ALL
SELECT 7689,'32723'
UNION ALL
SELECT 9516,'27651'
UNION ALL
SELECT 9899,'V5861'
UNION ALL
SELECT 2279,'78061'
GO
Here is my script really simplified down...
select
visit_ext_id
,icd9_code
from
#xicd9tempheader a
inner join #xicd9tempcodes b on a.icd9_int_id = b.icd9_int_id
where
icd9_code in ('1533', '4019', '2768', 'E8497', '27651', 'V1259')
order by visit_ext_id asc
The results are something like this...
123456 E8497
123456 V1259
123456 1533
234567 4019
567890 27651
567890 2768
Here's what I'd like the results to look like...
123456 E8497 V1259 1533
234567 4019
567890 27651 2768
Pseudo code would be cool. If not, please let me know what functions to look at in BOL...or google.
TIA,
John

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 1

Do you want separate columns or do you want a single column with items separated by spaces?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

corey lawson Hall of Fame Points: 3773 More actions · Answer 2

Hmm... ICD-9 codes... There's a bit more than 1024 different ICD-9 "base" codes...

but maybe he's only going to possibly have no more than 1023 distinct codes over a population (not that one patient will likely have that many!), so that's a constraint on a solution with one ICD-9 code/column...

I'd throw in a row_number() over (partition by 1 order by icd9code) column first, so potentially this could be used to ID the column with the ICD-9 code, whether by a PIVOT or a cross-join...

patientid id icd9code

12345 1 10102

12345 2 10124

...

to turn into some form of

patientid 1 2 ...

12345 10102 10124

or...

patientid icd9codes

12345 10102, 10124...

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

That's why I asked the question. The ROW_NUMBER() thing is necessary if the OP wants separate columns and isn't necessary if a space delimited, multi-element column is all that's required.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

J Moore Old Hand Points: 398 More actions · Answer 4

We don't expect more than 6-7 codes to show up on one patient account. The state registry supplied us a list of codes that were coded to patients over the last 2 years. I was given somewhat of a solution but it leaves a space or a comma after the last code and I don't want that.

I would like each code in a separate column but if it's far easier to put a list of them in the last column, I'm open for that as long as there is no space or comma after the last code.

J Moore Old Hand Points: 398 More actions · Answer 5

corey lawson (12/15/2011)
I'd throw in a row_number() over (partition by 1 order by icd9code) column first,

Is there a good example somewhere on how to use "row_number() over (partition by 1 order by icd9code)"

I've used this before but I used it to insert numbers into a column. I guess what I'm asking is how I'd use it in a cross-join.

I'll google around for an example in the mean time.

corey lawson Hall of Fame Points: 3773 More actions · Answer 6

one way is to just do something like...

[font="Courier New"]select acct_num, icd9_code,

row_number() over (partition by acct_num order by acct_num, icd9_code) as rn

[/font]

Put this in a sub-select or temp table if necessary...