Home » SQL & PL/SQL » SQL & PL/SQL » change display
( ) 1 Vote
change display [message #35138] |
Thu, 27 January 2005 06:05  |
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   |
Art Metzer
Messages: 2480 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>
|
|
|
|
How to solve this scenario [message #114961 is a reply to message #35139] |
Wed, 06 April 2005 10:54   |
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  |
Art Metzer
Messages: 2480 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>
|
|
|
Goto Forum:
Current Time: Mon Feb 17 01:53:45 CST 2025
|