#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2013
    Posts
    7
    Rep Power
    0

    Create Dynamic Pivot - multiple rows into fields based on 2 common fields


    How can I pivot the PONT_LOC field into separate fields for each value, thus creating one row per SECTION & SURVEY_DATE combination, the number of rows will always very based on SECTION & SURVEY_DATE, can someone please help? Oracle11g

    SELECT * FROM (SELECT STATE_CODE||SHRP_ID AS SECTION, SURVEY_DATE, POINT_LOC
    FROM (SELECT * FROM MON_DIS_JPCC_FAULT ORDER BY STATE_CODE, SHRP_ID, SURVEY_DATE, POINT_LOC)
    WHERE STATE_CODE=55
    AND SHRP_ID LIKE '0213'
    AND CRACK_OR_JOINT='J'
    AND SURVEY_DATE='04/27/1998');

    +---------+-------------+-----------+
    | SECTION | SURVEY_DATE | POINT_LOC |
    +---------+-------------+-----------+
    | 550213 | 04/27/1998 | 3.2 |
    +---------+-------------+-----------+
    | 550213 | 04/27/1998 | 7.9 |
    +---------+-------------+-----------+
    | 550213 | 04/27/1998 | 12.5 |
    +---------+-------------+-----------+
    | 550213 | 04/27/1998 | 17.1 |
    +---------+-------------+-----------+
    | 550213 | 04/27/1998 | 21.7 |
    +---------+-------------+-----------+
    | 550213 | 04/27/1998 | 26.2 |
    +---------+-------------+-----------+
    | 550213 | 04/27/1998 | 30.7 |
    +---------+-------------+-----------+
    | 550213 | 04/27/1998 | 35.2 |
    +---------+-------------+-----------+
    | 550213 | 04/27/1998 | 39.8 |
    +---------+-------------+-----------+
    | 550213 | 04/27/1998 | 44.4 |
    +---------+-------------+-----------+
    | 550213 | 04/27/1998 | 48.9 |
    +---------+-------------+-----------+
    | 550213 | 04/27/1998 | 53.5 |
    +---------+-------------+-----------+
    | 550213 | 04/27/1998 | 58 |
    +---------+-------------+-----------+
    | 550213 | 04/27/1998 | 62.8 |
    +---------+-------------+-----------+
    | 550213 | 04/27/1998 | 67.1 |
    +---------+-------------+-----------+
    | 550213 | 04/27/1998 | 71.7 |
    +---------+-------------+-----------+
    | 550213 | 04/27/1998 | 76.5 |
    +---------+-------------+-----------+
    | 550213 | 04/27/1998 | 81.2 |
    +---------+-------------+-----------+
    | 550213 | 04/27/1998 | 85.6 |
    +---------+-------------+-----------+
    | 550213 | 04/27/1998 | 90.2 |
    +---------+-------------+-----------+
    | 550213 | 04/27/1998 | 94.7 |
    +---------+-------------+-----------+
    | 550213 | 04/27/1998 | 99.4 |
    +---------+-------------+-----------+
    | 550213 | 04/27/1998 | 103.9 |
    +---------+-------------+-----------+
    | 550213 | 04/27/1998 | 108.3 |
    +---------+-------------+-----------+
    | 550213 | 04/27/1998 | 114 |
    +---------+-------------+-----------+
    | 550213 | 04/27/1998 | 117.7 |
    +---------+-------------+-----------+
    | 550213 | 04/27/1998 | 122.1 |
    +---------+-------------+-----------+
    | 550213 | 04/27/1998 | 126.7 |
    +---------+-------------+-----------+
    | 550213 | 04/27/1998 | 131.3 |
    +---------+-------------+-----------+
    | 550213 | 04/27/1998 | 136 |
    +---------+-------------+-----------+
    | 550213 | 04/27/1998 | 140.5 |
    +---------+-------------+-----------+
    | 550213 | 04/27/1998 | 144.9 |
    +---------+-------------+-----------+
    | 550213 | 04/27/1998 | 149.4 |
    +---------+-------------+-----------+
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2013
    Posts
    7
    Rep Power
    0
    Here is some sample data:

    CREATE TABLE MON_DIS_JPCC_FAULT
    (SECTION VARCHAR(255),
    SURVEY_DATE VARCHAR(255),
    POINT_LOC DECIMAL(3,1));

    INSERT INTO MON_DIS_JPCC_FAULT
    VALUES ('550213', '04/27/1998', 3.2);

    INSERT INTO MON_DIS_JPCC_FAULT
    VALUES ('550213', '04/27/1998', 7.9);

    INSERT INTO MON_DIS_JPCC_FAULT
    VALUES ('550213', '04/27/1998', 12.5);

    INSERT INTO MON_DIS_JPCC_FAULT
    VALUES ('550213', '04/27/1998', 17.1);

    INSERT INTO MON_DIS_JPCC_FAULT
    VALUES ('550213', '04/27/1998', 21.7);

    I don't know if my explanation is correct...here is a sample of the output I'm looking for:

    +--------------+----------------------+---------------------+---------------------+---------------------+---------------------+----------------------+
    | SECTION | SURVEY_DATE | POINT_LOC_1 | POINT_LOC_2 | POINT_LOC_3 | POINT_LOC_4 | POINT_LOC_5 |...6, 7, 8, etc...
    +--------------+----------------------+---------------------+---------------------+---------------------+---------------------+----------------------+
    | 550213 | 04/27/1998 | 3.2 | 7.9 | 12.5 | 17.1 | 21.7 |
    +--------------+----------------------+---------------------+---------------------+---------------------+---------------------+----------------------+

    Also, the number of POINT_LOC's is always going to be different.

    I hope this makes sense, and any assistance is greatly appreciated!

IMN logo majestic logo threadwatch logo seochat tools logo