Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Need Help in writing SQL QUERY

639485Nov 5 2008 — edited Nov 6 2008
Hi All,

I need your help to write the query for the following situation. The query should be SQL query. There is no front end concept involved here.
There is a table say ABC having following entries. You can think that Col1 contains standard, Col2 contains Name, Col3 and Col4 contains the grade.
Following is just an example and the number of rows in actual scenario is more.
Col1    Col2     Col3     Col4
 
5th     RAM      A1       C1 
5th     RAM      B1       D1 
6th     HARI     M1       N1
6th     HARI     P1       Q1 
6th     HARI     R1        S1
I need a query to create a view XYZ which will give me rows in following way. 
Col1      Col2    Col3            Col4
5th       RAM    A1,B1         C1,D1 
6th       HARI   M1,P1,R1    N1,Q1,S1
Thank you.

Comments

Gurjas
Try this query 
 with t as
(
select '5th' as col1,'RAM' as col2,'A1' as col3,'C1' as col4 from dual
union
select '5th','RAM','B1','D1' from dual
union
select '6th','HARI','M1','N1' from dual
union
select '6th','HARI','P1','Q1' from dual
union
select '6th','HARI','R1','S1' from dual
)
select
   col1, col2,
 LTRIM(MAX(SYS_CONNECT_BY_PATH(col3,',')) KEEP (DENSE_RANK LAST ORDER BY curr),',') AS req_col1,
 LTRIM(MAX(SYS_CONNECT_BY_PATH(col4,',')) KEEP (DENSE_RANK LAST ORDER BY curr),',') AS req_col2
from
   (
   select col1,col2,col3,col4,
           ROW_NUMBER() OVER (PARTITION BY col1,col2 ORDER BY col3, col4) AS curr,
           ROW_NUMBER() OVER (PARTITION BY col1,col2 ORDER BY col3, col4) -1 AS prev
   from t
   )
group by
 col1,col2
connect by prev =prior  curr AND col1 = PRIOR col1  START WITH curr=1
/
623666
B-)
 with t as
(
select '5th' as col1,'RAM' as col2,'A1' as col3,'C1' as col4 from dual
union
select '5th','RAM','B1','D1' from dual
union
select '6th','HARI','M1','N1' from dual
union
select '6th','HARI','P1','Q1' from dual
union
select '6th','HARI','R1','S1' from dual)
select wmsys.wm_concat(distinct col2) col2,
       wmsys.wm_concat(distinct col3) col3,
       wmsys.wm_concat(distinct col4) col4
from t
group by Col1;
Gurjas
wmsys.wm_concat is undocumented function.. it may be desupported by oracle in future..
It is not good to use it in production enviornment.
vhmangaiha
ORA-00904: "WMSYS"."WM_CONCAT": invalid identifier
am getting this error when i tried your query ...
Aketi Jyuuzou
I used documented feature only B-) 
create table t(col1,col2,col3,col4) as
select '5th','RAM' ,'A1','C1' from dual union
select '5th','RAM' ,'B1','D1' from dual union
select '6th','HARI','M1','N1' from dual union
select '6th','HARI','P1','Q1' from dual union
select '6th','HARI','R1','S1' from dual;

col col1 for a10
col col2 for a10
col col3 for a10
col col4 for a10

select col1,
Rtrim(substr(replace(replace(XMLAgg(XMLElement("dummy",col2) order by col2),'</dummy>'),'<dummy>',','),2),',') as col2,
Rtrim(substr(replace(replace(XMLAgg(XMLElement("dummy",col3) order by col3),'</dummy>'),'<dummy>',','),2),',') as col3,
Rtrim(substr(replace(replace(XMLAgg(XMLElement("dummy",col4) order by col4),'</dummy>'),'<dummy>',','),2),',') as col4
from (select col1,
      case Row_Number() over(partition by col1,col2 order by RowID) when 1 then col2 end as col2,
      case Row_Number() over(partition by col1,col3 order by RowID) when 1 then col3 end as col3,
      case Row_Number() over(partition by col1,col4 order by RowID) when 1 then col4 end as col4
        from t)
group by col1
order by col1;

COL1  COL2  COL3      COL4
----  ----  --------  --------
5th   RAM   A1,B1     C1,D1
6th   HARI  M1,P1,R1  N1,Q1,S1
Frank Kulash
Hi,

If you don't want to use WM_CONCAT, then use STRAGG, which does the same thing.

You can copy STRAGG from asktom.oracle.com

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2196162600402

The same page includes other techniques for doing String Aggregation, such as SYS_CONNECT_BY_PATH.
MichaelS
Aketi, why so complicated? ;)
SQL>  with t as
(
 select '5th' as col1,'RAM' as col2,'A1' as col3,'C1' as col4 from dual union 
 select '5th','RAM','B1','D1' from dual union
 select '6th','HARI','M1','N1' from dual union
 select '6th','HARI','P1','Q1' from dual union
 select '6th','HARI','R1','S1' from dual
)
select   col1, col2, 
         xmlagg (xmlelement (e, col3 || ',')).extract('//text()').getstringval() col3,
         xmlagg (xmlelement (e, col4 || ',')).extract('//text()').getstringval() col4
  from t
group by col1, col2
/
COL COL2 COL3                 COL4                
--- ---- -------------------- --------------------
5th RAM  A1,B1,               C1,D1,              
6th HARI M1,R1,P1,            N1,S1,Q1,           

2 rows selected.
Aketi Jyuuzou
OOPS :_|
Because my English is poor,
I misunderstood that SQL must return distinct string like JaneDoe's post.
666352
Hi, Another solution with model (10g) 
SQL> select * from t;

COL COL2 COL3                                               COL4
--- ---- -------------------------------------------------- ----------------------------------------
5th RAM  A1                                                 C1
5th RAM  B1                                                 D1
6th HARI M1                                                 N1
6th HARI P1                                                 Q1
6th HARI R1                                                 S1

SQL> SELECT  col1,col2, SUBSTR(v_col3,2) col3, SUBSTR(v_col4,2) col4
  2  FROM   t 
  3  MODEL
  4  RETURN UPDATED ROWS  
  5  PARTITION BY ( col1 )
  6  DIMENSION BY (ROW_NUMBER() OVER (PARTITION BY col1 ORDER BY col2 DESC) AS POSITION )
  7    MEASURES   ( col2, CAST( col3 AS VARCHAR2(3000) ) AS v_col3,
  8                        CAST( col4 AS VARCHAR2(3000) ) AS v_col4 ) IGNORE NAV
  9    RULES
 10      UPSERT
 11      ITERATE( 100)
 12        UNTIL ( PRESENTV(v_col3[ITERATION_NUMBER+2],1,0) = 0 and PRESENTV(v_col4[ITERATION_NUMBER+2],1,0) = 0 )
 13      ( v_col3[0] =v_col3[0] || ',' || v_col3[ ITERATION_NUMBER+1],
 14      v_col4[0] =v_col4[0] || ',' || v_col4[ ITERATION_NUMBER+1],
 15      col2[0]=col2[ ITERATION_NUMBER+1] )
 16  ORDER BY col1;

COL COL2 COL3                                               COL4
--- ---- -------------------------------------------------- ----------------------------------------
5th RAM  B1,A1                                              D1,C1
6th HARI P1,R1,M1                                           Q1,S1,N1

SQL>
1 - 9
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Dec 4 2008
Added on Nov 5 2008
9 comments
3,663 views