View from pivoted data

  • 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!

  • 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'

    .....

     

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

  • 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!

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

  • Farrell,

     

    I did find your post. But the temp table can't be used in a view if my memory is correct.

     

  • Don't know what to tell you...  It has to be a View? 

     

    I wasn't born stupid - I had to study.

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

  • It will be used with other views to submit data to the .gov.. so a view is the ideal solution.

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

  • 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