2 Replies Latest reply: Dec 4, 2012 2:08 AM by jeneesh RSS

    group by

      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 
      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
          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
            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;