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!

query to task

Discussion in 'SQL PL/SQL' started by prabhur, Mar 13, 2015.

  1. prabhur

    prabhur Active Member

    Messages:
    24
    Likes Received:
    0
    Trophy Points:
    80
    I have a table with column names system _unique_no, connection point, x, y, z. when i found system_unique_no and connection point is 1 insert values x,y,z in x1, y1,z1. when i found system_unique_no and connection point is 2 insert values x,y,z in x2, y2,z2 in new column in same row in same table
     
  2. RG Hegde

    RG Hegde Forum Advisor

    Messages:
    185
    Likes Received:
    35
    Trophy Points:
    310
    Location:
    Pune
    Hello,
    As per my understanding column x1,y1,z1 is available in the table and need to insert the values from the column x,y,z to x1,y1,z1 if the connection point value is 1 . Correct me if I am wrong.

    Could you please elaborate the requirement.

    If you provide sample data and expected output it will help us to provide the query.
     
  3. prabhur

    prabhur Active Member

    Messages:
    24
    Likes Received:
    0
    Trophy Points:
    80
    I have a table like this

    System_
    unique_no connectio_point X Y Z
    ---------- ------------- ----- -----
    223 1 100 25 125
    223 2 125 100 25
    163 1 235 280 290
    163 2 350 370 450
    163 3 450 565 580

    I want result like this

    System_
    unique_no X1 Y1 Z1 X2 Y2 Z2 X3 Y3 Z3
    ---------- ------------- ----- ----- ------------- -----
    223 100 25 125 125 100 25

    163 235 280 290 350 370 450 450 565 580
     
  4. prabhur

    prabhur Active Member

    Messages:
    24
    Likes Received:
    0
    Trophy Points:
    80
    hello ,
    I have given sample data and expected output. You have understand the requirement correctly.
     
  5. jagadekara

    jagadekara Forum Guru

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

    We can display like below by using XML AGG.


    System_unique_no X1 Y1 Z1
    ------------------ -------- --------- -----------------
    223 100,125 125,100 125,25

    163 235,350,450 280,370,565 290,450,580
     
  6. eras

    eras Active Member

    Messages:
    23
    Likes Received:
    9
    Trophy Points:
    90
    Location:
    Lithuania
    --in 11g
    Code (SQL):

    SELECT *
    FROM (SELECT system_unique_no,
                 connection_point,
                 val_code,
                 vals
          FROM (SELECT *
                FROM your_table
                        unpivot (vals FOR val_code IN("X", "Y", "Z"))))
    pivot(MAX(vals) FOR (connection_point, val_code) IN ((1, 'X') AS "X1",
                                                         (1, 'Y') AS "Y1",
                                                         (1, 'Z') AS "Z1",
                                                         (2, 'X') AS "X2",
                                                         (2, 'Y') AS "Y2",
                                                         (2, 'Z') AS "Z2",
                                                         (3, 'X') AS "X3",
                                                         (3, 'Y') AS "Y3",
                                                         (3, 'Z') AS "Z3"))
     
     
  7. prabhur

    prabhur Active Member

    Messages:
    24
    Likes Received:
    0
    Trophy Points:
    80
    I have a table with column names system _unique_no, connection point, cp_easting, cp_northing, cp_elevation and some other columns. when i found system_unique_no and connection point is 1 insert values , cp_easting, cp_northing, cp_elevation in x1, y1,z1. when i found system_unique_no and connection point is 2 insert values , cp_easting, cp_northing, cp_elevation in x2, y2,z2 in new column in same row in same table.

    I have a table like this

    System_
    unique_no connectio_point cp_easting cp_northing cp_elevation
    ---------- ------------- ----- -----
    223 1 100 25 125
    223 2 125 100 25
    163 1 235 280 290
    163 2 350 370 450
    163 3 450 565 580

    I want result like this

    System_
    unique_no X1 Y1 Z1 X2 Y2 Z2 X3 Y3 Z3
    ---------- ------------- ----- ----- ------------- -----
    223 100 25 125 125 100 25

    163 235 280 290 350 370 450 450 565 580
     
  8. prabhur

    prabhur Active Member

    Messages:
    24
    Likes Received:
    0
    Trophy Points:
    80
    SELECT *
    FROM (SELECT system_unique_no,
    connection_point,
    val_code,
    vals
    FROM (SELECT *
    FROM pdtable_170
    unpivot (vals FOR val_code IN("cp_easting", "cp_northing", "cp_elevation"))))
    pivot(MAX(vals) FOR (connection_point, val_code) IN ((1, 'cp_easting') AS "X1",
    (1, 'cp_northing') AS "Y1",
    (1, 'cp_elevation') AS "Z1",
    (2, 'cp_easting') AS "X2",
    (2, 'cp_northing') AS "Y2",
    (2, 'cp_elevation') AS "Z2",
    (3, 'cp_easting') AS "X3",
    (3, 'cp_northing') AS "Y3",
    (3, 'cp_elevation') AS "Z3"));

    I have changed column name as need. It shows error "cp_elevation":invalid identifer
     
  9. eras

    eras Active Member

    Messages:
    23
    Likes Received:
    9
    Trophy Points:
    90
    Location:
    Lithuania
    --use UPPER column names
    Code (SQL):

    SELECT *
    FROM (SELECT system_unique_no,
    connection_point,
    val_code,
    vals
    FROM (SELECT *
    FROM pdtable_170
    unpivot (vals FOR val_code IN("CP_EASTING", "CP_NORTHING", "CP_ELEVATION"))))
    pivot(MAX(vals) FOR (connection_point, val_code) IN ((1, 'CP_EASTING') AS "X1",
    (1, 'CP_NORTHING') AS "Y1",
    (1, 'CP_ELEVATION') AS "Z1",
    (2, 'CP_EASTING') AS "X2",
    (2, 'CP_NORTHING') AS "Y2",
    (2, 'CP_ELEVATION') AS "Z2",
    (3, 'CP_EASTING') AS "X3",
    (3, 'CP_NORTHING') AS "Y3",
    (3, 'CP_ELEVATION') AS "Z3"));
     
     
    prabhur likes this.
  10. prabhur

    prabhur Active Member

    Messages:
    24
    Likes Received:
    0
    Trophy Points:
    80
    Hi,
    Thanks very much. I want to know how this query works