Home » SQL & PL/SQL » SQL & PL/SQL » transpose logic (oracle)
| transpose logic [message #685496] |
Tue, 25 January 2022 04:58  |
 |
suji6281
Messages: 130
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
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
Regards
Suji
|
|
|
|
| Re: transpose logic [message #685498 is a reply to message #685496] |
Tue, 25 January 2022 07:22  |
Solomon Yakobson
Messages: 3214
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>
P.S. You can also use PIVOT.
|
|
|
|
|
|
|
|
|
|
| Re: transpose logic [message #685528 is a reply to message #685498] |
Sat, 29 January 2022 13:13 |
|
suji6281
Messages: 130
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
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
Regards
SUji
|
|
|
|
| Re: transpose logic [message #685529 is a reply to message #685528] |
Sat, 29 January 2022 18:31 |
Solomon Yakobson
Messages: 3214
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>
|
|
|
|
Goto Forum:
Current Time: Thu Mar 30 21:15:27 CDT 2023
|
] 
Search
Help
Members
Register
Login
Home
