October 27, 2005 at 1:17 pm
Greetings all
I have a table with this structure:
l_id char(3) not null
v_id char (12) not null
diag_code char (5) not null
diag_desc varchar (3) null
seq_nbr char (2) not null
c_id char (12) not null
createdate datetime
moddate datetime
visit_detail:
l_id v_id diag_code diag_desc seq_nbr c_id createdate moddate
001 003 111 <string> 00 777 2001-01-01 2001-01-01
001 003 1567 <string> 01 777 2001-01-01 2001-01-01
001 003 5555 <string> 02 777 2001-01-01 2001-01-01
001 003 7289 <string> 03 777 2001-01-01 2001-01-01
I'd like to return the following in a view:
l_id v_id diag_code1 diag_code2 diag_code3 diag_code4 c_id createdate moddate
001 003 111 1567 5555 7289 777 2001-01-01 2001-01-01
Some visit id's have only one diagnostic code.. but any could contain 1 to 4 diag_codes.
I can't use a temp table in a view so I'm wondering what the forums minds have for suggestions? The table is somewhat deep with 1/2 million rows. I'd rather not use a cursor. But I'm stuck on how to best do this.
Thanks!
October 27, 2005 at 2:21 pm
Since there are only 4 possible diagnostic codes
You could create a view using left joins
select groupedvid.v_id,
from
(SELECT DISTINCT v_id
FROM dbo.mydiagnose mydiagnose
) AS groupedvid
INNER JOIN
(select l_id,c_id,v_id,createdate,moddate
FROM dbo.mydiagnose mydiagnose
) AS visitinfo
on groupedvid.v_id=visitinfo.v_id
LEFT JOIN dbo.mydiagnose diagseq00 /*diagnostic code sequence 1*/
ON groupedvid.v_id =diagseq00.v_id
AND diagseq00.seq_nbr='00'
LEFT JOIN dbo.mydiagnose diagseq01 /*diagnostic code sequence 1*/
ON groupedvid.v_id =diagseq01.v_id
AND diagseq01.seq_nbr='01'
.....
October 27, 2005 at 2:29 pm
I have done this with Dynamic SQL when you do not know your number of columns. As Jo points out, since you already know that information, I would highly recommend her approach!
I wasn't born stupid - I had to study.
October 27, 2005 at 2:51 pm
I apologize for not being more clear but after staring at this for a good part of the day Im getting a little thick..
I should clarify:
There are thousands of different possible diag_codes.. I do know the max number of columns for the diag_codes columns will be a range of 1 to 4 since select (max)seq_nbr returns 4
however the seq_nbr would rank the column order from left to right as Primary diag, Secondary diag etc..
so seq_nbr 00 is 1st
seq_nbr 01 is 2nd
seq_nbr 02 is 3rd
seq_nbr 03 is 4th
Many of the visit id's have just one diag_code with that ranked as seq_nbr = 00.. but quite a few could have anywhere from 00 - 03 seq_nbr associated with the visit
I just want to flip the diag_codes where there is count > 1 into 1 row preserving the order by seq_nbr.
to me this should be relatively simple.. but I'm just drawing a blank.
I will take a look at this tomorrow.. hopefully a few more posts will jar my head loose.
Thanks for taking the time with your solution Jo!
October 27, 2005 at 3:26 pm
This is a poor solution and I would highly recommend you search this site for Pivot Tables, but here goes: Clumsy Pivot Table Solution
I wasn't born stupid - I had to study.
October 27, 2005 at 3:37 pm
Farrell,
I did find your post. But the temp table can't be used in a view if my memory is correct.
October 27, 2005 at 3:42 pm
Don't know what to tell you... It has to be a View?
I wasn't born stupid - I had to study.
October 27, 2005 at 6:42 pm
Is this for a report or an application? In other words, will it be used occassionally or continuously by multiple users?
I'm thinking you may be able to try populating a physical table and have your first statement be DROP TABLE - not sure if this will work in a view... If this is used continuously, then that is not an option.
Hopefully you have been able to search this site and find a better solution...
I wasn't born stupid - I had to study.
October 28, 2005 at 9:33 am
It will be used with other views to submit data to the .gov.. so a view is the ideal solution.
October 28, 2005 at 9:34 am
How's this?
CREATE TABLE #T1 (
l_id char(3) not null,
v_id char (12) not null,
diag_code char (5) not null,
diag_desc varchar (10) null,
seq_nbr char (2) not null,
c_id char (12) not null,
createdate datetime,
moddate datetime)
INSERT INTO #T1
SELECT '001', '003', '111', '<string>', '00', '777', '2001-01-01', '2001-01-01' UNION ALL
SELECT '001', '003', '1567', '<string>', '01', '777', '2001-01-01', '2001-01-01' UNION ALL
SELECT '001', '003', '5555', '<string>', '02', '777', '2001-01-01', '2001-01-01' UNION ALL
SELECT '001', '003', '7289', '<string>', '03', '777', '2001-01-01', '2001-01-01' UNION ALL
SELECT '002', '003', '111', '<string>', '00', '777', '2001-01-01', '2001-01-01' UNION ALL
SELECT '002', '003', '1567', '<string>', '01', '777', '2001-01-01', '2001-01-01' UNION ALL
SELECT '002', '003', '5555', '<string>', '02', '777', '2001-01-01', '2001-01-01' UNION ALL
SELECT '002', '003', '7289', '<string>', '03', '777', '2001-01-01', '2001-01-01'
SELECT
l_id, v_id,
MAX(CASE seq_nbr WHEN '00' THEN diag_code END) AS diag_code1,
MAX(CASE seq_nbr WHEN '01' THEN diag_code END) AS diag_code2,
MAX(CASE seq_nbr WHEN '02' THEN diag_code END) AS diag_code3,
MAX(CASE seq_nbr WHEN '03' THEN diag_code END) AS diag_code4,
c_id, createdate, moddate
FROM #T1
GROUP BY l_id, v_id, c_id, createdate, moddate
DROP TABLE #T1
It gives...
l_id v_id diag_code1 diag_code2 diag_code3 diag_code4 c_id createdate moddate
---- ----- ---------- ---------- ---------- ---------- ----- ----------- -----------
001 003 111 1567 5555 7289 777 2001-01-01 2001-01-01
002 003 111 1567 5555 7289 777 2001-01-01 2001-01-01
Regards,
Ryan
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
October 31, 2005 at 10:59 am
Ryan,
Perfect! Thanks so much.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply