CODEWITHSUNDEEP
sqloracle-database
ad2387
ad2387

Reputation: 571

Oracle - How to group by column based on another column

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!

Upvotes: 1

Views: 2351

Answers (2)

Gaston Flores
Gaston Flores

Reputation: 2467

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.

Upvotes: 1

MK.
MK.

Reputation: 34527

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.

Upvotes: 0

Related Questions