Home » SQL & PL/SQL » SQL & PL/SQL » transpose logic (oracle)
transpose logic [message #685496] Tue, 25 January 2022 04:58 Go to next message
suji6281
Messages: 134
Registered: September 2014
Senior Member
Hi Team,

Requesting you please help me with the SQL query which displays the output in the below format.
I have tried with transpose logic to display muliple CUST_PHONE rows into single row but didn't get the results properly.
And if the customer has multiple addresses then we should display the FLAG1 and FLAG2 as "YES" else it should be "NO".
Note: PHONE_TYPE's are maximum there only for any customer.

below are the tables which helps for your quick reference.

CREATE TABLE CUST (
    CUST_ID  int,
    CUST_NAME  varchar(255)
 );

CREATE TABLE CUST_ADDR (
    CUST_ID  int,
    CUST_NAME  varchar(255),
    SEQ_NO  int,
    ADDR1      varchar(255),
    ADDR2     varchar(255),
    STATE     varchar(255)
);
CREATE TABLE CUST_PHONE (
    CUST_ID  int,
    CUST_NAME  varchar(255),
    SEQ_NO  int,
    PHONE_TYPE    varchar (10),
    PH_NO   varchar(25)
);
INSERT INTO CUST (CUST_ID, CUST_NAME)  VALUES (1021, 'STARS');
INSERT INTO CUST (CUST_ID, CUST_NAME)  VALUES (1022, 'LEO');
INSERT INTO CUST (CUST_ID, CUST_NAME)  VALUES (1023, 'AOSMITH');
INSERT INTO CUST_ADDR  (CUST_ID, CUST_NAME, SEQ_NO , ADDR1, ADDR2, STATE)  VALUES (1021, 'STARS', 1, STREET23, 2-34-901, TEXAS);
INSERT INTO CUST_ADDR  (CUST_ID, CUST_NAME, SEQ_NO , ADDR1, ADDR2, STATE)  VALUES (1021, 'STARS', 2, STREET76, 7-51-118, TEXAS);
INSERT INTO CUST_ADDR  (CUST_ID, CUST_NAME, SEQ_NO , ADDR1, ADDR2, STATE)  VALUES (1022, 'LEO',   1, MAIN ROAD, LINE-26, TEXAS);
INSERT INTO CUST_ADDR  (CUST_ID, CUST_NAME, SEQ_NO , ADDR1, ADDR2, STATE)  VALUES (1023, 'AOSMITH', 1, STREET23, 2-34-905, TEXAS);
INSERT INTO CUST_PHONE (CUST_ID, SEQ_NO , PHONE_TYPE, PH_NO)  VALUES (1021, 1, 'WORK', '2297810912');
INSERT INTO CUST_PHONE (CUST_ID, SEQ_NO , PHONE_TYPE, PH_NO)  VALUES (1021, 1, 'HOME', '8906611785');
INSERT INTO CUST_PHONE (CUST_ID, SEQ_NO , PHONE_TYPE, PH_NO)  VALUES (1021, 1, 'FAX', '8175558090');
INSERT INTO CUST_PHONE (CUST_ID, SEQ_NO , PHONE_TYPE, PH_NO)  VALUES (1021, 2, 'HOME', '8906611785');
INSERT INTO CUST_PHONE (CUST_ID, SEQ_NO , PHONE_TYPE, PH_NO)  VALUES (1021, 1, 'WORK', '2297810913');

CUST_ID    CUST_NAME
1021       STARS
1022       LEO
1023       AOSMITH



CUST_ID    CUST_NAME   SEQ_NO     ADDR1        ADDR2     STATE
1021        STARS      1         STREET23     2-34-901   TEXAS
1021        STARS      2         STREET76     7-51-118   TEXAS
1022        LEO        1         MAIN ROAD    LINE-26    TEXAS
1023        AOSMITH    1         STREET23     2-34-905   TEXAS


CUST_ID    SEQ_NO   PHONE_TYPE    PH_NO
1021       1        WORK          2297810912
1021       1        HOME          8906611785
1021       1        FAX           8175558090
1021       2        HOME          8906611785
1022       1        WORK          2297810913  


OUTPUT:


CUST_ID  CUST_NAME  SEQ_NO  ADDR1      STATE  PH_TYPE1   PH_NO1     PH_TYPE2   PH_NO2     PH_TYPE3   PH_NO3        FLAG1    FLAG2
1021     STARS      1       STREET23   TEXAS  WORK      2297810912  HOME       8906611785 FAX        8175558090    YES      YES
1021     STARS      2       STREET76   TEXAS                        HOME       8906611785                          YES      YES
1022     LEO        1       MAIN ROAD  TEXAS  WORK      2297810913                                                  NO       NO
1023     AOSMITH    1       STREET23   TEXAS                                                                        NO       NO

Thank you.

Regards
Suji
Re: transpose logic [message #685498 is a reply to message #685496] Tue, 25 January 2022 07:22 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
You didn'y enclose address strings in quotes and you also have a typo in phones. It should be:

INSERT INTO CUST_PHONE (CUST_ID, SEQ_NO , PHONE_TYPE, PH_NO) VALUES (1022, 1, 'WORK', '2297810913');

Anyway:

SELECT  C.CUST_ID,
        C.CUST_NAME,
        A.SEQ_NO,
        A.ADDR1,
        A.STATE,
        MAX(CASE P.PHONE_TYPE WHEN 'WORK' THEN PHONE_TYPE END) PH_TYPE1,
        MAX(CASE P.PHONE_TYPE WHEN 'WORK' THEN PH_NO END) PH_NO1,
        MAX(CASE P.PHONE_TYPE WHEN 'HOME' THEN PHONE_TYPE END) PH_TYPE2,
        MAX(CASE P.PHONE_TYPE WHEN 'HOME' THEN PH_NO END) PH_NO2,
        MAX(CASE P.PHONE_TYPE WHEN 'FAX' THEN PHONE_TYPE END) PH_TYPE3,
        MAX(CASE P.PHONE_TYPE WHEN 'FAX' THEN PH_NO END) PH_NO3,
        CASE
          WHEN COUNT(*) OVER(PARTITION BY C.CUST_ID) > 1 THEN 'YES'
          ELSE 'NO'
        END FLAG1,
        CASE
          WHEN COUNT(*) OVER(PARTITION BY C.CUST_ID) > 1 THEN 'YES'
          ELSE 'NO'
        END FLAG2
  FROM      CUST C
        LEFT JOIN
            CUST_ADDR A
          ON A.CUST_ID = C.CUST_ID
        LEFT JOIN
            CUST_PHONE P
          ON     P.CUST_ID = C.CUST_ID
             AND
                 P.SEQ_NO = A.SEQ_NO
  GROUP BY C.CUST_ID,
           C.CUST_NAME,
           A.SEQ_NO,
           A.ADDR1,
           A.STATE
  ORDER BY C.CUST_ID,
           A.SEQ_NO
/

   CUST_ID CUST_NAME     SEQ_NO ADDR1                STATE PH_TYPE1   PH_NO1     PH_TYPE2   PH_NO2     PH_TYPE3   PH_NO3     FLA FLA
---------- --------- ---------- -------------------- ----- ---------- ---------- ---------- ---------- ---------- ---------- --- ---
      1021 STARS              1 STREET23             TEXAS WORK       2297810912 HOME       8906611785 FAX        8175558090 YES YES
      1021 STARS              2 STREET76             TEXAS                       HOME       8906611785                       YES YES
      1022 LEO                1 MAIN ROAD            TEXAS WORK       2297810913                                             NO  NO
      1023 AOSMITH            1 STREET23             TEXAS                                                                   NO  NO

SQL>
SY.
P.S. You can also use PIVOT.
Re: transpose logic [message #685504 is a reply to message #685498] Tue, 25 January 2022 10:44 Go to previous messageGo to next message
suji6281
Messages: 134
Registered: September 2014
Senior Member
Thank you Solomon for correcting the data and providing the solution. Good Day.
Re: transpose logic [message #685518 is a reply to message #685496] Wed, 26 January 2022 08:22 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
There is a flaw in your data model. You should not be repeating CUST_NAME in every table. It belongs only in the CUST table. You only need CUST_ID in all of the tables (as a FK back to the CUST table) to tie them together. This is actually a fundamental rule of data design. With the model you have, if a customer name changes, you need to make sure you change it in all tables. I guarantee that it is only a matter of time before something goes wrong with that and a given customer (identified by CUST_ID) ends up with different names in different tables.

And 255 characters for STATE? Really?
Re: transpose logic [message #685527 is a reply to message #685518] Sat, 29 January 2022 12:52 Go to previous messageGo to next message
suji6281
Messages: 134
Registered: September 2014
Senior Member
Hi EdStevens,

Thanks for checking the data and table structure.
I have modified the syntax of table(s).

Regards
Suji
Re: transpose logic [message #685528 is a reply to message #685498] Sat, 29 January 2022 13:13 Go to previous messageGo to next message
suji6281
Messages: 134
Registered: September 2014
Senior Member
Hi Solomon,

I have modified the table(s) structure and required output was changed as mentioned below.
Here you go with the tables and data. Address type should be max. of two rows with address type as 'domestic' and 'pay' in CUST_ADDR table.

if more than 1 address...1st line should be domestic as NO and pay as YES,....2nd line should be domestic as YES and pay as N0
if customer has one address only then .... ordering as YES and pay as YES.
please see the output for your quick reference.


CREATE TABLE CUST (
    CUST_ID  int,
    CUST_NAME  varchar(255)
 );

CREATE TABLE CUST_ADDR (
    CUST_ID  int,
    SEQ_NO  int,
    ADDR_TYPE varchar(10),
    ADDR1      varchar(255),
    ADDR2     varchar(255),
    STATE     varchar(10)
);
CREATE TABLE CUST_PHONE (
    CUST_ID  int,
    SEQ_NO  int,
    PHONE_TYPE    varchar (10),
    PH_NO   varchar(25)
);
INSERT INTO CUST (CUST_ID, CUST_NAME)  VALUES (1021, 'STARS');
INSERT INTO CUST (CUST_ID, CUST_NAME)  VALUES (1022, 'LEO');
INSERT INTO CUST (CUST_ID, CUST_NAME)  VALUES (1023, 'AOSMITH');
INSERT INTO CUST_ADDR  (CUST_ID, SEQ_NO , ADDR_TYPE, ADDR1, ADDR2, STATE)  VALUES (1021, 1, 'DOMESTIC', 'STREET23', '2-34-901', 'TEXAS');
INSERT INTO CUST_ADDR  (CUST_ID, SEQ_NO , ADDR_TYPE,  ADDR1, ADDR2, STATE)  VALUES (1021, 2, 'PAY', 'STREET76', '7-51-118', 'TEXAS');
INSERT INTO CUST_ADDR  (CUST_ID, SEQ_NO , ADDR_TYPE,  ADDR1, ADDR2, STATE)  VALUES (1022, 1, 'DOMESTIC', 'MAIN ROAD', 'LINE-26', 'TEXAS');
INSERT INTO CUST_ADDR  (CUST_ID, SEQ_NO , ADDR_TYPE,  ADDR1, ADDR2, STATE)  VALUES (1023, 1, 'PAY', 'STREET23', '2-34-905', 'TEXAS');
INSERT INTO CUST_PHONE (CUST_ID, SEQ_NO , PHONE_TYPE, PH_NO)  VALUES (1021, 1, 'WORK', '2297810912');
INSERT INTO CUST_PHONE (CUST_ID, SEQ_NO , PHONE_TYPE, PH_NO)  VALUES (1021, 1, 'HOME', '8906611785');
INSERT INTO CUST_PHONE (CUST_ID, SEQ_NO , PHONE_TYPE, PH_NO)  VALUES (1021, 1, 'FAX', '8175558090');
INSERT INTO CUST_PHONE (CUST_ID, SEQ_NO , PHONE_TYPE, PH_NO)  VALUES (1021, 2, 'HOME', '8906611785');
INSERT INTO CUST_PHONE (CUST_ID, SEQ_NO , PHONE_TYPE, PH_NO)  VALUES (1022, 1, 'WORK', '2297810913');

CUST_ID    CUST_NAME
1021       STARS
1022       LEO
1023       AOSMITH



CUST_ID    SEQ_NO    ADDR_TYPE    ADDR1        ADDR2     STATE
1021        1        DOMESTTIC    STREET23     2-34-901   TEXAS
1021        2        PAY          STREET76     7-51-118   TEXAS
1022        1        DOMESTTIC    MAIN ROAD    LINE-26    TEXAS
1023        1        PAY          STREET23     2-34-905   TEXAS


CUST_ID    SEQ_NO   PHONE_TYPE    PH_NO
1021       1        WORK          2297810912
1021       1        HOME          8906611785
1021       1        FAX           8175558090
1021       2        HOME          8906611785
1022       1        WORK          2297810913  


OUTPUT:


CUST_ID  CUST_NAME  SEQ_NO  ADDR1      STATE  PH_NO1      PH_NO2       PH_NO3        DOMESTIC    PAY
1021     STARS      1       STREET23   TEXAS  2297810912  8906611785   8175558090    NO          YES
1021     STARS      2       STREET76   TEXAS              8906611785                 YES         NO
1022     LEO        1       MAIN ROAD  TEXAS  2297810913                             YES         YES      
1023     AOSMITH    1       STREET23   TEXAS                                         YES         YES      

Thank you.

Regards
SUji
Re: transpose logic [message #685529 is a reply to message #685528] Sat, 29 January 2022 18:31 Go to previous message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Not much of a difference:

SELECT  C.CUST_ID,
        C.CUST_NAME,
        A.SEQ_NO,
        A.ADDR1,
        A.STATE,
        MAX(CASE P.PHONE_TYPE WHEN 'WORK' THEN PHONE_TYPE END) PH_TYPE1,
        MAX(CASE P.PHONE_TYPE WHEN 'WORK' THEN PH_NO END) PH_NO1,
        MAX(CASE P.PHONE_TYPE WHEN 'HOME' THEN PHONE_TYPE END) PH_TYPE2,
        MAX(CASE P.PHONE_TYPE WHEN 'HOME' THEN PH_NO END) PH_NO2,
        MAX(CASE P.PHONE_TYPE WHEN 'FAX' THEN PHONE_TYPE END) PH_TYPE3,
        MAX(CASE P.PHONE_TYPE WHEN 'FAX' THEN PH_NO END) PH_NO3,
        CASE ROW_NUMBER() OVER(PARTITION BY C.CUST_ID ORDER BY A.SEQ_NO)
          WHEN COUNT(*) OVER(PARTITION BY C.CUST_ID) THEN 'YES'
          ELSE 'NO'
        END DOMESTIC,
        CASE
          WHEN COUNT(*) OVER(PARTITION BY C.CUST_ID) = 1 THEN 'YES'
          WHEN ROW_NUMBER() OVER(PARTITION BY C.CUST_ID ORDER BY A.SEQ_NO) = 1 THEN 'YES'
          ELSE 'NO'
        END PAY
  FROM      CUST C
        LEFT JOIN
            CUST_ADDR A
          ON A.CUST_ID = C.CUST_ID
        LEFT JOIN
            CUST_PHONE P
          ON     P.CUST_ID = C.CUST_ID
             AND
                 P.SEQ_NO = A.SEQ_NO
  GROUP BY C.CUST_ID,
           C.CUST_NAME,
           A.SEQ_NO,
           A.ADDR1,
           A.STATE
  ORDER BY C.CUST_ID,
           A.SEQ_NO
/

CUST_ID CUST_NAME     SEQ_NO ADDR1      STATE      PH_TYPE1   PH_NO1     PH_TYPE2   PH_NO2     PH_TYPE3   PH_NO3     DOMESTIC PAY
------- --------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -------- ---
   1021 STARS              1 STREET23   TEXAS      WORK       2297810912 HOME       8906611785 FAX        8175558090 NO       YES
   1021 STARS              2 STREET76   TEXAS                            HOME       8906611785                       YES      NO
   1022 LEO                1 MAIN ROAD  TEXAS      WORK       2297810913                                             YES      YES
   1023 AOSMITH            1 STREET23   TEXAS                                                                        YES      YES

SQL>
SY.
Previous Topic: ORA 00922 MISSING OR INVALID OPTION
Next Topic: ORA-04068: existing state of packages has been discarded
Goto Forum:
  


Current Time: Thu Mar 28 21:28:27 CDT 2024