Sign in or 
Location: The Official Oracle Wiki
Discussion: Select ROWs in one column
Category:
Discussion Forum /
General Discussion
Share this
Showing 7 posts
aamirmam |
Select ROWs in one column
Dec 23 2007, 11:22 AM EST
how to SQL for selecting rows in one row.Data EMPID DEPT ------------ -------------- 1 A 1 B 1 C Required EMPID DEPT ------------ -------------- 1 A, B, C Please any one can write SQL for above. Best regards Aamir Mughal 25 out of 49 found this valuable. Do you? |
jonah.harris |
1. RE: Select ROWs in one column
Dec 23 2007, 11:39 AM EST
You can use something like MULTISET:CREATE TYPE varchar2_table_t AS TABLE OF VARCHAR2(32767); SELECT empid, CAST(MULTISET(SELECT dept FROM emp WHERE empid = e.empid) AS varchar2_table_t) FROM emp e GROUP BY empid; 4 out of 25 found this valuable. Do you? |
|
dbnem |
2. RE: Select ROWs in one column
Dec 23 2007, 4:10 PM EST
Google for "STRAGG" particulary on the ask.tom.1 out of 20 found this valuable. Do you? |
|
jonah.harris |
3. RE: Select ROWs in one column
Dec 23 2007, 4:27 PM EST
Forgot to mention, this is actually a question best suited for the OTN forums. Particularly, the SQL & PL/SQL forum (http://forums.oracle.com/forums/forum.jspa?forumID=75)There are a couple other resources for ya: http://www.oracle-base.com/articles/10g/StringAggregationTechniques.php 2 out of 22 found this valuable. Do you? |
|
aamirmam |
4. RE: Select ROWs in one column
Dec 24 2007, 8:38 AM EST
Thank you very much harris
5
out of
27 found this valuable.
Do you?
|
|
qube |
5. RE: Select ROWs in one column
Feb 28 2008, 4:16 PM EST
actually it's called pivot queries, more information and examples could be found here:http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:766825833740 3 out of 11 found this valuable. Do you? |
|
srini.ms |
6. RE: Select ROWs in one column
Mar 11 2009, 2:17 PM EDT
excelent reply from http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:15637744429336original -------- SELECT * FROM STAGE; CU PR OR OPERATOR -- -- -- --------- C1 P1 D1 OP1 C1 P2 D2 OP2 C1 P3 D3 OP3 C1 P1 D4 OP3 converted to rows ------------------ SELECT customer, MAX(LTRIM( sys_connect_by_path( product, ',' ) , ',')) prod_str, MAX(LTRIM( sys_connect_by_path( order_date , ',' ) , ',')) order_str, MAX(LTRIM( sys_connect_by_path( OPERATOR , ',' ) , ',')) OPERATOR_str FROM ( SELECT customer, product , order_date , OPERATOR, row_number() over (PARTITION BY customer ORDER BY ROWID) rn FROM STAGE ) START WITH rn = 1 CONNECT BY customer = PRIOR customer AND PRIOR rn = rn -1 GROUP BY customer; Do you find this valuable? |
