Home » SQL & PL/SQL » SQL & PL/SQL » Help in Listagg Query??? (Oracle 11g)
Help in Listagg Query??? [message #658402] |
Tue, 13 December 2016 02:48 |
|
arifs3738
Messages: 39 Registered: November 2015 Location: India
|
Member |
|
|
SCRIPTS TO CREATE TABLE & DATA
CREATE TABLE T36
(
CUSTOMER_ID VARCHAR2(5),
DEVICE_SEQUENCE NUMBER(3),
DEVICE_NAME VARCHAR2(25)
);
INSERT INTO T36 VALUES ('C1',1,'IPHONE 4S');
INSERT INTO T36 VALUES ('C1',2,'GALAXY TAB');
INSERT INTO T36 VALUES ('C1',3,'IPAD');
INSERT INTO T36 VALUES ('C2',1,'IPHONE 4G');
INSERT INTO T36 VALUES ('C2',2,'IPAD');
TABLE
CUSTOMER_ID DEVICE_SEQUENCE DEVICE_NAME
C1 1 IPHONE 4S
C1 2 GALAXY TAB
C1 3 IPAD
C2 1 IPHONE 4G
C2 2 IPAD
OUTPUT REQUIRED
CUSTOMER_ID DEVICE_TYPE_1 DEVICE_TYPE_2 DEVICE_TYPE_3
C1 IPHONE 4S GALAXY TAB IPAD
C2 IPHONE 4G IPAD NULL
Query i have written...
SELECT CUSTOMER_ID,
SUBSTR (DEVICE_NAME, 1, INSTR (DEVICE_NAME, ',', 1, 1)-1) AS DEVICE_TYPE_1,
SUBSTR (DEVICE_NAME,
INSTR (DEVICE_NAME, ',', 1, 1) +1,
(INSTR (DEVICE_NAME, ',', 1, 2)-1) - INSTR (DEVICE_NAME, ',', 1,1)) AS DEVICE_TYPE_2,
SUBSTR (DEVICE_NAME, INSTR (DEVICE_NAME, ',', 1, 2)+1) AS DEVICE_TYPE_3
FROM
(SELECT CUSTOMER_ID, LISTAGG (DEVICE_NAME, ',') WITHIN GROUP (ORDER BY DEVICE_SEQUENCE) AS DEVICE_NAME
FROM T36
GROUP BY CUSTOMER_ID);
My Query Output
CUSTOMER_ID DEVICE_TYPE_1 DEVICE_TYPE_2 DEVICE_TYPE_3
C1 IPHONE 4S GALAXY TAB IPAD
C2 IPHONE 4G NULL IPHONE 4G,IPAD
For second row i am not getting the expected output as the values are not sufficient.
Please help me to solve the above sql...Thanks
|
|
|
|
Re: Help in Listagg Query??? [message #658404 is a reply to message #658402] |
Tue, 13 December 2016 03:01 |
|
Michel Cadot
Messages: 68665 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Don't use LISTAGG, what you want to do is to pivot the data, so use PIVOT:
SQL> select *
2 from t36
3 pivot (max(device_name)
4 for device_sequence in
5 (1 "DEVICE_TYPE_1",
6 2 "DEVICE_TYPE_2",
7 3 "DEVICE_TYPE_3"))
8 order by customer_id
9 /
CUSTO DEVICE_TYPE_1 DEVICE_TYPE_2 DEVICE_TYPE_3
----- ------------------------- ------------------------- -------------------------
C1 IPHONE 4S GALAXY TAB IPAD
C2 IPHONE 4G IPAD
2 rows selected.
[Updated on: Tue, 13 December 2016 03:03] Report message to a moderator
|
|
|
Re: Help in Listagg Query??? [message #658405 is a reply to message #658403] |
Tue, 13 December 2016 03:14 |
pablolee
Messages: 2882 Registered: May 2007 Location: Scotland
|
Senior Member |
|
|
To add however, when you come acrposs a situation such as this i.e. when an expression that contains multiple components does not return what you expect it to, evaluate each individual component, rather that trying to identify the issue by evaluating the entire expression.
Hint, have a look at
INSTR (DEVICE_NAME, ',', 1, 2)
To see where you might have gone wrong
|
|
|
Goto Forum:
Current Time: Fri Jun 28 10:02:31 CDT 2024
|