This discussion is archived
2 Replies Latest reply: Dec 4, 2012 12:08 AM by jeneesh RSS

group by

00125 Explorer
Currently Being Moderated
Hi all,

I having a table like this
CREATE TABLE #TableName   
(
     Name varchar(100),
     startDate varchar(100),
     Active varchar(1),
     EndDAte varchar(15),
     Title Varchar(100),
     Manager varchar(100),
     Department_Text varchar(100),
     [Level] int
)
INSERT INTO #TableName
select 'Vincent McMohan', '1971-01-01','Y',NULL,'Quality Assurance Manager','Selig','Enginerring',1 UNION ALL
select 'Vincent McMohan', '1971-01-01','Y',NULL,'Quality Assurance Manager','Selig','Quality Assurance',2 UNION ALL
select 'Patricia Stratigias', '2004-04-14','Y',NULL,'Senior Quality Assurance Manager','Francona','Enginerring',1 UNION ALL
select 'Patricia Stratigias', '2004-04-14','Y',NULL,'Senior Quality Assurance Manager','Francona','Quality Assurance',2 UNION ALL
select 'Ted DiBeasi', '1975-06-01','N','2002-08-22','Quality Assurance Engineer','Francona','Enginerring',1 UNION ALL
select 'Ted DiBeasi', '1975-06-01','N','2002-08-22','Quality Assurance Engineer','Francona','Quality Assurance',2 
commit;
The result.
In the above record name,title,manager are grouped together and get maximum of their enddate and startdate and also get their department_type in single column

Output like this
Name                          Title                                     department_type                         max(enddate)             max(startdate)       
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Vincent McMohan        Quality Assurance Manager       Enginerring,Quality Assurance                    1971-01-01                      NULL         
version is 10g
Thanks in advance

Edited by: 00125 on Dec 3, 2012 11:42 PM
  • 1. Re: group by
    908002 Expert
    Currently Being Moderated
    what s ur oracle version

    if 11g , use pivot with grooup by / list agg with group by

    other than 11g, use connect_by_path with group by
  • 2. Re: group by
    jeneesh Guru
    Currently Being Moderated
    The posted codes are having syntax errors..

    You could use SYS_CONNECT_BY_PATH
    select name,title,
          ltrim(max(sys_connect_by_path(department_text,',')),',') dep_text,
          max(enddate) end_dt,
          max(startdate) s_dt
    from test_table
    start with lvl = 1
     connect by lvl = prior lvl + 1
     and prior name = name
    group by  name,title;

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points