Home » SQL & PL/SQL » SQL & PL/SQL » SQL Query
SQL Query [message #192154] Mon, 11 September 2006 05:09 Go to next message
gp185007
Messages: 45
Registered: April 2005
Location: Mumbai
Member
Hi Friends,

I am having one requirement, which i am explaining with simple example.

I am having one table say test with script as

CREATE TABLE test
(x NUMBER,
y NUMBER)

and the data as

select * from test;

X Y
1 2
1 1
1 3
2 1
2 2
3 1
3 2
3 3
3 4

Now i want to write one query which will give me output in follwing format

Col1 col2

1 2,1,3
2 1,2
3 1,2,3,4

col1 contains all distinct entries of column X and col2 contains all column Y entries separated by , for that distinct entry.





Re: SQL Query [message #192159 is a reply to message #192154] Mon, 11 September 2006 05:32 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Try to choose a topic title that says something about your subject. You are looking for a mechanism that is described here often enough. I suggest you search the board. CONCAT_ALL or STRAGG offer a performant way out.

MHE
Re: SQL Query [message #192170 is a reply to message #192154] Mon, 11 September 2006 06:04 Go to previous messageGo to next message
goudelly
Messages: 52
Registered: August 2006
Location: India
Member

Hi,

It is possible in pl/sql not in single sql query.

Thanks,

Mohan Reddy
Re: SQL Query [message #192193 is a reply to message #192154] Mon, 11 September 2006 07:21 Go to previous messageGo to next message
shoblock
Messages: 325
Registered: April 2004
Senior Member
got to asktom.com and search on PIVOT
Re: SQL Query [message #192202 is a reply to message #192170] Mon, 11 September 2006 07:38 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
goudelly wrote on Mon, 11 September 2006 13:04

Hi,

It is possible in pl/sql not in single sql query.

Thanks,

Mohan Reddy

Yes it is possible in SQL. SYS_CONNECT_BY_PATH can be useful. But I would still opt for CONCAT_ALL/STRAGG:

CREATE TABLE mhe_foo(col1 NUMBER, col2 NUMBER)
/

INSERT INTO mhe_foo VALUES(1, 2 );
INSERT INTO mhe_foo VALUES(1, 1 );
INSERT INTO mhe_foo VALUES(1, 3 );
INSERT INTO mhe_foo VALUES(2, 1 );
INSERT INTO mhe_foo VALUES(2, 2 );
INSERT INTO mhe_foo VALUES(3, 1 );
INSERT INTO mhe_foo VALUES(3, 2 );
INSERT INTO mhe_foo VALUES(3, 3 );
INSERT INTO mhe_foo VALUES(3, 4 );

col col2 format a20

SELECT col1
     , CONCAT_ALL(CONCAT_EXPR(col2,',')) col2
FROM mhe_foo
GROUP BY col1
/

SELECT col1
     , MAX(SUBSTR(SYS_CONNECT_BY_PATH(col2,','),2)) col2
FROM   ( SELECT col1
              , col2 
              , row_number() over ( partition by col1 order by col2 ) rn
         FROM   mhe_foo
       )
CONNECT BY PRIOR col1 = col1
AND        PRIOR nvl(rn,0) > rn
GROUP BY col1
/


DROP TABLE mhe_foo
/


The concat_all code is on the board. For the last time: search. The asktom advice is a good one, it is the source of the code I used.

MHE
Re: SQL Query [message #192204 is a reply to message #192154] Mon, 11 September 2006 07:43 Go to previous messageGo to next message
goudelly
Messages: 52
Registered: August 2006
Location: India
Member

Hi,

This is povit table :


select job,
max( decode( deptno, 10, cnt, null ) ) dept_10,
max( decode( deptno, 20, cnt, null ) ) dept_20,
max( decode( deptno, 30, cnt, null ) ) dept_30,
max( decode( deptno, 40, cnt, null ) ) dept_40
from ( select job, deptno, count(*) cnt
from emp
group by job, deptno )
group by job


Thanks,

Mohan Reddy G

Re: SQL Query [message #192207 is a reply to message #192204] Mon, 11 September 2006 07:48 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
goudelly wrote on Mon, 11 September 2006 14:43

Hi,

This is povit table
..
<snip>
..
Thanks,

Mohan Reddy G
No thanks but this is not an answer to the question. The OP tries to concatenate values of column 2 grouped by column 1.

MHE
Previous Topic: Date in Group By Clause
Next Topic: Query Needed
Goto Forum:
  


Current Time: Sun Dec 11 00:33:53 CST 2016

Total time taken to generate the page: 0.04159 seconds