Oracle - How to group by column based on another column

Question

I have put together a query to list a bunch of related data. Below is a simplified version of the query as well as a sample output.

SELECT DISTINCT
  A.NAME AS APP_NAME,
  REPLACE(H.ENVIRONMENT,',','/') AS HOST_ENVIRONMENT,
  S.NAME AS SERVER_NAME,
  (SELECT 
      LISTAGG(HS.NAME_1, '/') WITHIN GROUP(ORDER BY HS.NAME_1)
       FROM 
         HOST_SERVER HS
   WHERE 
      HS.NAME_2 = S.NAME
  ) AS HOST_NAMES,
  CASE
      WHEN S.LOCATION_ID IN (22,28,59) THEN 'ABC'
      WHEN S.LOCATION_ID IN (6,8,9,11,53) THEN 'DBA'
      WHEN S.LOCATION_ID IN (5,41,52,43,61) THEN 'ADFS'
      WHEN S.LOCATION_ID IN (24,27,24,34,62,63,73) THEN 'ADR'
      ELSE NULL
  END AS HOST_LOCATION
FROM
  APP A
  FULL OUTER JOIN APP_HOST AH ON A.ID = AH.ID_1
  FULL OUTER JOIN HOST H ON AH.ID_2 = H.ID
  FULL OUTER JOIN HOST_SVR HS ON H.ID = HS.ID_1
  FULL OUTER JOIN SVR S ON HS.ID_2 = S.ID
GROUP BY 
  A.NAME,
  H.ENVIRONMENT,
  S.NAME,
  H.NAME,
  S.LOCATION_ID,
  S.LOCATION
ORDER BY
  A.NAME

Example Output (not complete, some unimportant columns left out):

APP_NAME        HOST_ENVIRONMENT      SERVER_NAME       HOST_NAMES
ABCD            Production            server1           host1/host2/host3
ABCD            Production            server2           host4/host5/host6
ABCD            Development           server1           host1/host2/host3
ABCD            UBD                   server2           host4/host5/host6

What I'd like to accomplish is to essentially group the APP_NAME by the Environment. I want one line for Production and one line for anything else (just called Development is fine). Below is an example of what I want:

APP_NAME    HOST_ENVIRONMENT   SERVER_NAME       HOST_NAMES
ABCD        Production         server1/server2   host1/host2/host3/host4/host5/host6            
ABCD        Development        server1/server2   host1/host2/host3/host4/host5/host6

How can I group these Applications by the Environment? Obviously this is a very simplified version but I'm dealing with thousands of rows. Thanks for any help!

Maybe [this](http://stackoverflow.com/questions/4686543/sql-to-concatenate-column-values-from-multiple-rows) can help — Lamak, Jul 09 '13 at 19:21
can you show us your sample data from all the tables used in your query? — rs., Jul 09 '13 at 19:22

Gaston Flores · Answer 1 · 2013-07-09T20:33:42.720

Please see following query, maybe will be useful for you:

CREATE TABLE test(
  APP_NAME         VARCHAR2(100),
  HOST_ENVIRONMENT VARCHAR2(100),
  SERVER_NAME      VARCHAR2(100),
  HOST_NAMES       VARCHAR2(100)
  );

INSERT INTO test VALUES('ABCD','Production','server1','host1/host2/host3');
INSERT INTO test VALUES('ABCD','Production','server2','host4/host5/host6');
INSERT INTO test VALUES('ABCD','Development','server1','host1/host2/host3');
INSERT INTO test VALUES('ABCD','UBD','server2','host4/host5/host6');

/* CHECK THIS QUERY */
SELECT APP_NAME,HOST_ENVIRONMENT,
listagg(SERVER_NAME,'/' ) WITHIN GROUP (ORDER BY SERVER_NAME) AS SERVER_NAME,
listagg(HOST_NAMES,'/' ) WITHIN GROUP (ORDER BY HOST_NAMES) AS HOST_NAMES
FROM test
GROUP BY APP_NAME,HOST_ENVIRONMENT

For try this please see this link

NOTE: this is solution for grouping and concatenacion problem, not a complety solution for your problem, you must post more information (for example, tables, data, etc).

I know it is not a good solution but you can replace my table TEST with your query, and I think you'll have what you need.

score 0 · Answer 2 · answered Jul 09 '13 at 19:25

0

You can group by multiple columns, e.g. group by app_name, host_environment and then use listagg aggregate function to combine server_names into a single string. Your data doesn't show how you want to handle multiple values in app_name column.

answered Jul 09 '13 at 19:25

MK.

33,605
18
74
111

I basically just want two rows for each APP_NAME, Production and Development. The next Application, 'EFGH', would also have Production and Development for example. – ad2387 Jul 09 '13 at 19:38
@ad2387 well, then what I said is correct-- group by these 2 columns and aggregate the rest. – MK. Jul 09 '13 at 19:40

Oracle - How to group by column based on another column

2 Answers2