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 Go to next message
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 #658403 is a reply to message #658402] Tue, 13 December 2016 03:00 Go to previous messageGo to next message
pablolee
Messages: 2847
Registered: May 2007
Location: Scotland
Senior Member
Rather than ListAgg, why are you not using PIVOT syntax, which is more appropriate based on your required output?
Re: Help in Listagg Query??? [message #658404 is a reply to message #658402] Tue, 13 December 2016 03:01 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
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 Go to previous message
pablolee
Messages: 2847
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
Previous Topic: AND criteria in where clause
Next Topic: Sending a Password protected PDF using utl_smtp
Goto Forum:
  


Current Time: Thu Jan 18 17:03:08 CST 2018

Total time taken to generate the page: 0.08095 seconds