Home » SQL & PL/SQL » SQL & PL/SQL » change display  () 1 Vote
change display [message #35138] Thu, 27 January 2005 06:05 Go to next message
Oliver
Messages: 30
Registered: April 1999
Member
hey guys,
if i need a selection to be from this display:
1 123
1 124
1 125
2 159
2 160
2 161

to be concatinated as such :
1 123,124,125
2 159,160,161

thanx,
Oliver
Re: change display [message #35139 is a reply to message #35138] Thu, 27 January 2005 06:37 Go to previous messageGo to next message
Art Metzer
Messages: 2478
Registered: December 2002
Senior Member
Your options:
  • The MAX(DECODE(...)) pivoting idiom
  • The KEEP/SYS_CONNECT_BY_PATH idiom (9i and greater)
  • Generating and using the stragg function (9i and greater)
  • A function that wraps a cursor
The MAX(DECODE(...)) pivoting idiom:
SQL> COLUMN as_string FORMAT A20
SQL> SELECT   x.id
  2  ,        MAX(DECODE(x.rn,1,TO_CHAR(x.value)))
  3           || MAX(DECODE(x.rn,2,',' || TO_CHAR(x.value)))
  4           || MAX(DECODE(x.rn,3,',' || TO_CHAR(x.value)))
  5           || MAX(DECODE(x.rn,4,',' || TO_CHAR(x.value)))
  6           || MAX(DECODE(x.rn,5,',' || TO_CHAR(x.value))) as_string
  7  FROM    (SELECT t.id
  8           ,      t.value
  9           ,      ROW_NUMBER()
 10                  OVER (PARTITION BY t.id
 11                        ORDER BY     t.value)    rn
 12           FROM   t)                             x
 13  GROUP BY x.id
 14  /
 
        ID AS_STRING
---------- --------------------
         1 123,124,125
         2 159,160,161
 
SQL>
The KEEP/SYS_CONNECT_BY_PATH idiom (9i and greater):
SQL> COLUMN as_string FORMAT A20
SQL> SELECT x.id
  2  ,      MAX(SUBSTR(SYS_CONNECT_BY_PATH(TO_CHAR(x.value)
  3                    ,                   ',')
  4             ,      2))
  5         KEEP(DENSE_RANK LAST ORDER BY x.curr)    as_string
  6  FROM   (SELECT t.id
  7          ,      t.value
  8          ,      ROW_NUMBER()
  9                 OVER (PARTITION BY t.id
 10                       ORDER BY     t.value)      curr
 11          ,      ROW_NUMBER()
 12                 OVER (PARTITION BY t.id
 13                       ORDER BY     t.value) - 1  prev
 14          FROM   t) x
 15  GROUP BY   x.id
 16  START WITH x.curr = 1
 17  CONNECT BY x.prev = PRIOR x.curr
 18  AND        x.id   = PRIOR x.id
 19  /
 
        ID AS_STRING
---------- --------------------
         1 123,124,125
         2 159,160,161
 
SQL>
Generating and using the stragg function (9i and greater):
SQL> CREATE OR REPLACE TYPE string_agg_type AS OBJECT (
  2      total VARCHAR2(4000)
  3  ,   STATIC FUNCTION odciaggregateinitialize (
  4                          sctx IN OUT string_agg_type)
  5      RETURN NUMBER
  6  ,   MEMBER FUNCTION odciaggregateiterate (
  7                          self    IN OUT  string_agg_type
  8                      ,   value   IN      VARCHAR2)
  9      RETURN NUMBER
 10  ,   MEMBER FUNCTION odciaggregateterminate (
 11                          self        IN  string_agg_type
 12                      ,   returnvalue OUT VARCHAR2
 13                      ,   flags       IN  NUMBER)
 14      RETURN NUMBER
 15  ,   MEMBER FUNCTION odciaggregatemerge (
 16                          self    IN OUT  string_agg_type
 17                      ,   ctx2    IN      string_agg_type)
 18      RETURN NUMBER
 19  );
 20  /
 
Type created.
 
SQL> CREATE OR REPLACE TYPE BODY string_agg_type AS
  2      STATIC FUNCTION odciaggregateinitialize (
  3                          sctx IN OUT string_agg_type)
  4      RETURN NUMBER
  5      IS
  6      BEGIN
  7          sctx := string_agg_type(NULL);
  8          RETURN (odciconst.success);
  9      END odciaggregateinitialize;
 10
 11      MEMBER FUNCTION odciaggregateiterate (
 12                          self    IN OUT  string_agg_type
 13                      ,   value   IN      VARCHAR2)
 14      RETURN NUMBER
 15      IS
 16      BEGIN
 17          self.total := self.total || ',' || value;
 18          RETURN (odciconst.success);
 19      END odciaggregateiterate;
 20
 21      MEMBER FUNCTION odciaggregateterminate (
 22                          self        IN  string_agg_type
 23                      ,   returnvalue OUT VARCHAR2
 24                      ,   flags       IN  NUMBER)
 25      RETURN NUMBER
 26      IS
 27      BEGIN
 28          returnvalue := LTRIM(self.total,',');
 29          RETURN (odciconst.success);
 30      END odciaggregateterminate;
 31
 32      MEMBER FUNCTION odciaggregatemerge (
 33                          self    IN OUT  string_agg_type
 34                      ,   ctx2    IN      string_agg_type)
 35      RETURN NUMBER
 36      IS
 37      BEGIN
 38          self.total := self.total || ctx2.total;
 39          RETURN (odciconst.success);
 40      END odciaggregatemerge;
 41  END;
 42  /
 
Type body created.
 
SQL> CREATE OR REPLACE FUNCTION stragg (input VARCHAR2)
  2  RETURN VARCHAR2
  3  PARALLEL_ENABLE AGGREGATE USING string_agg_type;
  4  /
 
Function created.
 
SQL> COLUMN as_string FORMAT A20
SQL> SELECT   t.id
  2  ,        stragg(TO_CHAR(t.value)) as_string
  3  FROM     t
  4  GROUP BY t.id
  5  /
 
        ID AS_STRING
---------- --------------------
         1 123,124,125
         2 159,160,161
 
SQL>
A function that wraps a cursor:
SQL> CREATE OR REPLACE FUNCTION concat_values (p_id IN t.id%TYPE)
  2  RETURN VARCHAR2
  3  IS
  4      l_concatenated              VARCHAR2(4000);
  5  BEGIN
  6      FOR i_val IN (SELECT   TO_CHAR(t.value) value
  7                    FROM     t
  8                    WHERE    t.id = p_id
  9                    ORDER BY t.value)
 10      LOOP
 11          l_concatenated := l_concatenated
 12                            || ','
 13                            || i_val.value;
 14      END LOOP;
 15      RETURN (SUBSTR(l_concatenated,2));
 16  END concat_values;
 17  /
 
Function created.
 
SQL> COLUMN as_string FORMAT A20
SQL> SELECT   x.id
  2  ,        concat_values(x.id) as_string
  3  FROM    (SELECT   t.id
  4           FROM     t
  5           GROUP BY t.id) x
  6  /
 
        ID AS_STRING
---------- --------------------
         1 123,124,125
         2 159,160,161
 
SQL>
thanx again. Re: change display [message #35149 is a reply to message #35139] Thu, 27 January 2005 21:06 Go to previous messageGo to next message
Oliver
Messages: 30
Registered: April 1999
Member
ur beeing really helpfull thanx

Oliver
How to solve this scenario [message #114961 is a reply to message #35139] Wed, 06 April 2005 10:54 Go to previous messageGo to next message
john1811
Messages: 1
Registered: April 2005
Location: India
Junior Member

Hi,
I saw a solution given by you. My requirement is also the same with little changing column. Can you help me out.

The problem is:
I have a table with datas as below. I have given the script to create the table with below.

Sal_Date, Sal, ENAME
01-APR-05, 1000, A
01-APR-05, 2000, B
01-APR-05, 3000, C
05-May-05, 4000, A
05-May-05, 5000, B
05-May-05, 6000, C
05-May-05, 7000, D


******Start of Script********

create table Emp_Sal
(
Sal_Date date,
Sal number(5),
Ename varchar2(25)
);
insert into Emp_Sal values('01-Apr-05',1000,'A');
insert into Emp_Sal values('01-Apr-05',2000,'B');
insert into Emp_Sal values('01-Apr-05',3000,'C');
insert into Emp_Sal values('05-Apr-05',4000,'A');
insert into Emp_Sal values('05-Apr-05',5000,'B');
insert into Emp_Sal values('05-Apr-05',6000,'C');
insert into Emp_Sal values('05-Apr-05',7000,'D');

commit;

******End of Script********

When I give a SQL statement I have to get the output like below.

Sal_Date, A, B, C, D
--------- ----- ----- ----- -----
01-APR-05, 1000, 2000, 3000,
05-May-05, 4000, 5000, 6000, 7000

When we use the SQL solution provided by you we can get it. But what if the ENAME gets increased. Then will we be able to get the result using SQL Query alone or we have to use Stored procedures/Functions.

Thanks and Regards,
John Solomon. J
Re: How to solve this scenario [message #114982 is a reply to message #114961] Wed, 06 April 2005 12:02 Go to previous message
Art Metzer
Messages: 2478
Registered: December 2002
Senior Member
If you want the column headers to be dynamic, then you'll have to resort to dynamic SQL or ref cursors.
SQL> CREATE TABLE emp_sal (
  2      sal_date    DATE
  3  ,   sal         NUMBER(5)
  4  ,   ename       VARCHAR2(25)
  5  )
  6  /
 
Table created.
 
SQL> INSERT INTO emp_sal VALUES(TO_DATE('01-Apr-2005','DD-Mon-YYYY'),1000,'A');
SQL> INSERT INTO emp_sal VALUES(TO_DATE('01-Apr-2005','DD-Mon-YYYY'),2000,'B');
SQL> INSERT INTO emp_sal VALUES(TO_DATE('01-Apr-2005','DD-Mon-YYYY'),3000,'C');
SQL> INSERT INTO emp_sal VALUES(TO_DATE('05-Apr-2005','DD-Mon-YYYY'),4000,'A');
SQL> INSERT INTO emp_sal VALUES(TO_DATE('05-Apr-2005','DD-Mon-YYYY'),5000,'B');
SQL> INSERT INTO emp_sal VALUES(TO_DATE('05-Apr-2005','DD-Mon-YYYY'),6000,'C');
SQL> INSERT INTO emp_sal VALUES(TO_DATE('05-Apr-2005','DD-Mon-YYYY'),7000,'D');
 
SQL> ALTER SESSION SET NLS_DATE_FORMAT='DD-Mon-YYYY'
  2  /
 
Session altered.
 
SQL> CREATE OR REPLACE PROCEDURE p (p_rc OUT SYS_REFCURSOR)
  2  AS
  3      l_sql       VARCHAR2(32700) := 'SELECT TRUNC(es.sal_date) sal_date';
  4      l_rc        SYS_REFCURSOR;
  5  BEGIN
  6      FOR c IN (SELECT DISTINCT es.ename FROM emp_sal es)
  7      LOOP
  8          l_sql := l_sql
  9                   || ',SUM(DECODE(es.ename,'''
 10                   || REPLACE(c.ename,'''','''''')
 11                   || ''',es.sal,0)) "'
 12                   || UPPER(SUBSTR(REPLACE(c.ename,'''',''''''),1,30))
 13                   || '" ';
 14      END LOOP;
 15      l_sql := l_sql
 16               || 'FROM emp_sal es GROUP BY TRUNC(es.sal_date)';
 17      OPEN p_rc FOR l_sql;
 18  END p;
 19  /
 
Procedure created.
 
SQL> VARIABLE rc REFCURSOR
SQL> EXEC p(:rc);
 
PL/SQL procedure successfully completed.
 
SQL> PRINT :rc
 
SAL_DATE             A          B          C          D
----------- ---------- ---------- ---------- ----------
01-Apr-2005       1000       2000       3000          0
05-Apr-2005       4000       5000       6000       7000
 
SQL>
Previous Topic: Add new column using select or PL/SQL
Next Topic: Tough sql : dare to solve this?
Goto Forum:
  


Current Time: Wed May 24 20:25:55 CDT 2017

Total time taken to generate the page: 0.78213 seconds