1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Case statement with values from same column-display as headers

Discussion in 'SQL PL/SQL' started by carswelljr, May 19, 2014.

  1. carswelljr

    carswelljr Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    I would like to write a query to pull this information out and display it as one row for each ID. I would like to have that data display as one record for that same patientID lke in table 2, instead of as 3 separate records (With the Description Column from table 1)


    example: table 1

    id, name, itemresponse, itemcode, Description

    123 Dee Jones Childrens Hospital SITE Location

    123 Dee Jones 04/22/2014 NEXTAPPT Next Appointment Date

    123 Dee Jones Was on Bed rest NOSHOW Reason Did Not Show



    table 2

    id, name, Location, Next Appointment Date, Reason Did Not Show

    123 Dee Jones Childrens Hospital 04/22/2014 Was on bed rest



    I have included the code I have been working on below my question, for me to get the desired results should I use the Case Statement and is this the correct way to do it? I have attached the code to this post.
    thank you
     

    Attached Files:

  2. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi,

    Can you please provide us:

    1) The structure of all the tables i.e SQL to create it
    2) The data which is present in the tables(Data which is being "inserted")
     
  3. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
  4. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
  5. carswelljr

    carswelljr Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    CREATE TABLE myTable1 (
    ID NUMBER(10,0), --customer unique ID

    DOB DATE, --Customer birthdate
    FirstName VARCHAR2(50), --customer first name
    LastName VARCHAR2(50), --customer last name

    ITEM_CODE VARCHAR2(50), --ITEM_CODE = SITE, NEXTAPPT, LASTDATE, could result in multiple rows for one customer if customer has multiple ITEM_CODES
    Description VARCHAR2(50) --Column value based on the ITEM_CODE.
    ITEM_RESPONSE VARCHAR2(50) --The value based on ITEM_CODE, eg. ITEM_CODE=SITE, ITEM_RESPONSE= Childrens Hospital.
    );


    Table 1 originally looks like this:
    CUST_ID NAME DOB DATE ITEM_CODE DESCRIPTION ITEM RESPONSE

    1377777 TODD SIMMONS 15-AUG-89 24-SEP-12 NEXTAPPT Next_Appointment 12/15/2012

    1377777 TODD SIMMONS 15-AUG-89 24-SEP-12 LASTDATE Last_Visit_Date 11/27/2012

    1377777 TODD SIMMONS 15-AUG-89 24-SEP-12 SITE Location Childrens Hospital

    1222222 JIM SMITH 14-SEP-94 24-SEP-12 SITE Location Cleveland Clinic

    1222222 JIM SMITH 14-SEP-94 24-SEP-12 LASTDATE Last_Visit_Date 11/01/2012

    1111111 JILL SCOTT 22-NOV-77 24-SEP-12 LASTDATE Last_Visit_Date 11/11/2011



    I would like to write the query to pull the data from table one and look like Table2 below. Instead of the Customer repeating and creating a new row when they have multiple Item_Codes, make the customer have one record and the values in the Description field become headers based on the Item_Codes and one record for the customer.


    CREATE TABLE myTable2 (
    ID NUMBER(10,0), --customer unique ID
    FirstName VARCHAR2(50), --customer first name
    LastName VARCHAR2(50), --customer last name
    DOB DATE, --Customer birthdate

    Location VARCHAR2(50) --Column name from Description Field in table 1, based on the ITEM_CODE = SITE.
    Next_Appointment VARCHAR2(50) --Column name from Description Field in table 1, based on the ITEM_CODE = NEXTAPPT.
    Last_Visit_Date VARCHAR2(50) --Column name from Description Field in table 1, based on the ITEM_CODE = LASTDATE .
    ITEM_RESPONSE VARCHAR2(50), --The value based on ITEM_CODE, eg. ITEM_CODE=SITE, ITEM_RESPONSE= Childrens Hospital.
    );

    Table 1 should look like this now:
    CUST_ID NAME DOB DATE Next_Appointment Last_Visit_Date Location

    1377777 TODD SIMMONS 15-AUG-89 24-SEP-12 12/15/2012 11/27/2012 Childrens Hospital
    1222222 JIM SMITH 14-SEP-94 24-SEP-12 11/01/2012 Cleveland Clinic
    1111111 JILL SCOTT 22-NOV-77 24-SEP-12 11/11/2011
     
  6. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
    IMO, you have an architecture problem.

    By your definition, any Customer can have multiple rows in Table 1...but it's not clear if those rows MUST be different. How many records for T. Simmons can there possibly be? How many Next Appointment, Last_Visit_Date or Location records can T.Simmons have? If it's multiple, then how do intend to determine which record makes it to Table 2? Or is that the root question?

    If Table 1 is not in production yet...then I'd suggest changing the table structure to match the true requirement (which is not self evident at the moment). If it is in production then you're working a solution to a changing requirement? Are you sure you need a Table 2? How about a view? Maybe a Pipelined Function? The given purpose of Table2 is really undefined so there's a zillion ways to go w/ this. If it's possible, I'd add a UID to Table1 for each row and consider a separate lookup table for the Item_Code/Description as well as the Reponse in the event those are repeatable domain values....but all of this is a guess as the root requirement is a little fuzzy.

    HTH

    CJ
     
  7. carswelljr

    carswelljr Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Thank you for your reply. You bring up interesting points that I would have corrected if I had any control of the database. The Customer can have multiple Appointments, Visits and Locations. The permutations seem endless. But I will only need to pull the records with the most recent DATE (Check-In Date). After simplifying my query would this work:
    Here's one way to get the results you requested:

    SELECT id

    , firstname || ' ' || lastname AS name

    , dob

    , MAX (CASE WHEN item_code = 'NEXTAPPT' THEN item_response END) AS next_appointment

    , MAX (CASE WHEN item_code = 'LASTDATE' THEN item_response END) AS last_visit_date

    , MAX (CASE WHEN item_code = 'SITE' THEN item_response END) AS location

    FROM mytable1

    GROUP BY id, firstname, lastname, dob

    ;
     
  8. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi,

    Above query would not work essentially because you're doing a MAX of a character field. For example, for the same ID, NAME, DOB
    1- MAX(item_response) in the case of 'NEXTAPPT' and 'LASTDATE' are dates stored as characters in the format MM/DD/YYYY and '12/15/2012' would be retrieved instead of '01/10/2013' (which is the most recent of the two)
    2- Similarly in the case of 'SITE', 'Cleveland Clinic' is > 'A new Clinic' even if 'A new Clinic' is the last location to have been recorded for a given ID.

    I've added an extra field in your mytable1 (record_date) - see SQL below and you can try the following query:

    Code (SQL):
    CREATE TABLE myTable1
      (
        ID            NUMBER(10,0), --customer unique ID
        DOB           DATE,         --Customer birthdate
        [COLOR="blue"]Record_Date   DATE,         --Last record or check-in date[/COLOR]
        FirstName     VARCHAR2(50), --customer first name
        LastName      VARCHAR2(50), --customer last name
        ITEM_CODE     VARCHAR2(50), --ITEM_CODE = SITE, NEXTAPPT, LASTDATE, could result in multiple rows for one customer if customer has multiple ITEM_CODES
        Description   VARCHAR2(50), --Column value based on the ITEM_CODE.
        ITEM_RESPONSE VARCHAR2(50)  --The value based on ITEM_CODE, eg. ITEM_CODE=SITE, ITEM_RESPONSE= Childrens Hospital.
      );

    SELECT ID,
      NAME,
      DOB ,
      MAX(
      CASE
        WHEN item_code = 'NEXTAPPT'
        THEN item_response
      END) AS next_appointment ,
      MAX(
      CASE
        WHEN item_code = 'LASTDATE'
        THEN item_response
      END) AS last_visit_date ,
      MAX(
      CASE
        WHEN item_code = 'SITE'
        THEN item_response
      END) AS location
    FROM
      (SELECT ID ,
        firstname
        || ' '
        || lastname AS NAME ,
        record_date,
        dob,
        item_code,
        MAX(record_date) OVER (PARTITION BY ID, item_code) max_date ,
        item_response
      FROM mytable1
      GROUP BY ID,
        firstname
        || ' '
        || lastname,
        record_date,
        dob,
        item_code,
        item_response
      )
    WHERE record_date = max_date
    GROUP BY ID,
      NAME,
      dob
    ORDER BY ID ;
    Regards,
    Rajen.