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!

Need query to comibine result of Query

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

  1. prabhur

    prabhur Active Member

    Messages:
    24
    Likes Received:
    0
    Trophy Points:
    80
    Hi,
    SELECT A.SYSTEM_UNIQUE_NO,A.PARTITION_NO,A.LINE_NUMBER_LABEL,A.LINE_ID, NULL COMMODITY_ID, NULL COMMODITY_NAME, NULL MODEL_CODE, D.CONNECT_POINT_NO,D.CP_EASTING as x1, D.CP_NORTHING as y1, D.CP_ELEVATION as z1 FROM PDTABLE_12 A INNER JOIN PDTABLE_170 D ON A.SYSTEM_UNIQUE_NO=D.TABLE_UNIQ_NO WHERE D.CONNECT_POINT_NO NOT LIKE'%-%' AND D.PARTITION_NO=171
    UNION
    SELECT A.SYSTEM_UNIQUE_NO,A.PARTITION_NO,A.LINE_NUMBER_LABEL,A.LINE_ID,
    B.SYSTEM_UNIQUE_NO AS COMMODITY_ID,B.COMMODITY_NAME, B.MODEL_CODE,
    D.CONNECT_POINT_NO ,D.CP_EASTING as x1, D.CP_NORTHING as y1, D.CP_ELEVATION as z1 FROM PDTABLE_12 A
    INNER JOIN PDTABLE_34 B ON A.SYSTEM_UNIQUE_NO = B.CP1_IDX_12
    INNER JOIN PDTABLE_170 D ON B.SYSTEM_UNIQUE_NO=D.TABLE_UNIQ_NO
    WHERE D.CONNECT_POINT_NO NOT LIKE'%-%' AND D.PARTITION_NO=171
    UNION
    SELECT A.SYSTEM_UNIQUE_NO,A.PARTITION_NO,A.LINE_NUMBER_LABEL, A.LINE_ID,
    B.SYSTEM_UNIQUE_NO AS COMMODITY_ID,B.COMMODITY_NAME, B.MODEL_CODE,
    D.CONNECT_POINT_NO,D.CP_EASTING as x1, D.CP_NORTHING as y1, D.CP_ELEVATION as z1 FROM PDTABLE_12 A
    INNER JOIN PDTABLE_50 B ON A.SYSTEM_UNIQUE_NO = B.END1_IDX_12
    INNER JOIN PDTABLE_170 D ON B.SYSTEM_UNIQUE_NO=D.TABLE_UNIQ_NO
    WHERE D.CONNECT_POINT_NO NOT LIKE'%-%' AND D.PARTITION_NO=171

    In this query I have joint tables , get result as in screen shot.

    From the result , 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


    I need result like this, connection_point column not needed in result;


    SYSTEM_UNIQUE_NO PARTITION_NO LINE_NUMBER_LABEL LINE_ID COMMODITY_ID COMMODITY_NAME MODEL_CODE X1 Y1 Z1 X2 Y2 Z2 X3 Y3 Z3

    By COMMODITY_ID we can fetch X1 Y1 Z1 X2 Y2 Z2 value., If COMMODITY_ID is empty fetch then SYSTEM_UNIQUE_NO can we can fetch X1 Y1 Z1 X2 Y2 Z2 value.


    I have attached screen shot above query.
     

    Attached Files:

    • scr.jpg
      scr.jpg
      File size:
      38.8 KB
      Views:
      9