Home » SQL & PL/SQL » SQL & PL/SQL » Query for scenario
Query for scenario [message #443641] Tue, 16 February 2010 12:15 Go to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
Hi,

Here below is the data in table i have.

Table :T1

Col1 Col2
1 A
1 B
2 A
2 B
2 C
3 E
3 F
3 G
3 H
3 I
.....
.........
..............

Output:

Col1 Col2
1 A,B
2 A,B,C
3 E,F,G,H,I

.....
.........
..............


I need to frame a query to get above output from the table T1.

Any Help really appreciated.

Thanks in advance

Re: Query for scenario [message #443642 is a reply to message #443641] Tue, 16 February 2010 12:23 Go to previous messageGo to next message
BlackSwan
Messages: 24907
Registered: January 2009
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.


SEARCH this forum for "PIVOT"!

[Updated on: Tue, 16 February 2010 12:23]

Report message to a moderator

Re: Query for scenario [message #443643 is a reply to message #443641] Tue, 16 February 2010 12:36 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The weekly question with the daily missing test case and missing version number.

The solution depends on the version number (with at least 3 decimals) and all the solutions have already been provided (I think we missed none of them).

Regards
Michel

[Updated on: Tue, 16 February 2010 12:37]

Report message to a moderator

Re: Query for scenario [message #443644 is a reply to message #443641] Tue, 16 February 2010 12:36 Go to previous messageGo to next message
flyboy
Messages: 1831
Registered: November 2006
Senior Member
Hi,
To get the idea, you may have a look at this post: http://www.dbforums.com/oracle/1653246-how-concatenate-query-output-nested-query.html#post6446438
Choose solution available for your Oracle version.
Re: Query for scenario [message #443647 is a reply to message #443641] Tue, 16 February 2010 12:52 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> with 
  2    data as (
  3      select deptno, ename,
  4             row_number() over (partition by deptno order by ename) rn,
  5             count(*) over (partition by deptno) cnt
  6      from emp
  7    )
  8  select deptno, 
  9         substr(sys_connect_by_path(ename,','),2) emp
 10  from data
 11  where rn = cnt
 12  connect by prior deptno = deptno and prior rn = rn-1
 13  start with rn = 1
 14  order by deptno
 15  /
    DEPTNO EMP
---------- ------------------------------------------------------------
        10 CLARK,KING,MILLER
        20 ADAMS,FORD,JONES,SCOTT,SMITH
        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

3 rows selected.

SQL> 
SQL> select deptno, wm_concat(ename) emp
  2  from emp
  3  group by deptno
  4  order by deptno
  5  /
    DEPTNO EMP
---------- ------------------------------------------------------------
        10 CLARK,KING,MILLER
        20 SMITH,FORD,ADAMS,SCOTT,JONES
        30 ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD

3 rows selected.
Re: Query for scenario [message #443663 is a reply to message #443641] Tue, 16 February 2010 21:18 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
StringAggregationTechniques

Search before posting.
Always post your Oracle version number.

Sriram
Re: Query for scenario [message #443668 is a reply to message #443641] Tue, 16 February 2010 22:13 Go to previous message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
Thanks to all for providing me the solution.
Previous Topic: how can i read blob data from database into my directory (merged by CM)
Next Topic: How to find a remote dependencies of a database object
Goto Forum:
  


Current Time: Tue Sep 27 11:06:21 CDT 2016

Total time taken to generate the page: 0.09110 seconds