Tuesday, 13 October 2015

Display column values in a row as semicolon separated values (Use of LISTAGG Function)

Requirement: I have a table CAR that contains various CAR_NAME and the type of FUEL used in the CAR as show below. 

Now my requirement is to show all the CAR_NAME as a semicolon separated values in a row based on the type of FUEL used in the CAR as shown below:

Solution: For solution to the above requirement we can use LISTAGG function of the Oracle. Before I start using LISTAGG function I would like to give you brief description on LISTAGG function.
The LISTAGG function concatenates values of the measure_column for each GROUP based on the order_by_clause. That is for a specified measure, LISTAGG orders data within each group specified in the ORDER BY clause and then concatenates the values of the measure column as:
  1. A single-set aggregate function, LISTAGG operates on all rows and returns a single output row.
  2. A group-set aggregate, the function operates on and returns an output row for each group defined by the GROUP BY clause.
  3. An analytic function, LISTAGG partitions the query result set into groups based on one or more expression in the query_partition_clause.
The syntax for the LISTAGG function in Oracle is shown below:
LISTAGG (measure_column [, 'delimiter'])WITHIN GROUP (order_by_clause)
[OVER (query_partition_clause)]
The arguments to the function are subject to the following rules:
  1. The measure_column can be any expression. Null values in the measure column are ignored.
  2. The delimiter designates the string that is to separate the measure values. This clause is optional and defaults to NULL.
  3. The order_by_clause determines the order in which the concatenated values are returned. The function is deterministic only if the ORDER BY column list achieved unique ordering.
The return data type is RAW if the measure column is RAW; otherwise the return value is VARCHAR2.
Note: The LISTAGG function can be used in the following versions of Oracle:
  1. Oracle 12c
  2. Oracle 11g Release 2
Demonstration: For solution of the above requirement follow the steps as shown below:

Step 1: Create a table CAR using the below query:
CREATE TABLE CARS
  (
    CAR_ID   NUMBER(10) PRIMARY KEY,
    CAR_NAME VARCHAR2(20),
    FUEL     VARCHAR2(10)
  );
Step 2: Insert some records in the CAR table using the below insert statements:
INSERT INTO CARS(CAR_ID,CAR_NAME,FUEL) VALUES(1,'BMW M1','Petrol');
INSERT INTO CARS(CAR_ID,CAR_NAME,FUEL) VALUES(2,'Lamborghini Countach','Diesel');
INSERT INTO CARS(CAR_ID,CAR_NAME,FUEL) VALUES(3,'Porsche Carrera GT','Petrol'); 
INSERT INTO CARS(CAR_ID,CAR_NAME,FUEL) VALUES(4,'Nissan GT-R','Diesel');
INSERT INTO CARS(CAR_ID,CAR_NAME,FUEL) VALUES(5,'Ferrari Testarossa','Diesel');
INSERT INTO CARS(CAR_ID,CAR_NAME,FUEL) VALUES(6,'BAC Mono','Petrol');
INSERT INTO CARS(CAR_ID,CAR_NAME,FUEL) VALUES(7,'Lexus LFA','Diesel');
INSERT INTO CARS(CAR_ID,CAR_NAME,FUEL) VALUES(8,'Enzo Ferrari','Petrol');
INSERT INTO CARS(CAR_ID,CAR_NAME,FUEL) VALUES(9,'Mercedes-Benz','Diesel');
INSERT INTO CARS(CAR_ID,CAR_NAME,FUEL) VALUES(10,'Ferrari F40','Petrol');
Step 3: Use the select clause to see the inserted records in the CAR table.
SELECT * FROM CARS;
Thus the output is shown below:

Step 4: Now use the below query to display the required solution. Here we will use the LISTAGG function of the Oracle.
SELECT FUEL,LISTAGG(CAR_NAME, '; ') WITHIN GROUP (
ORDER BY CAR_NAME) "Car Name"
FROM CARS
GROUP BY FUEL
  ORDER BY FUEL;


Hence, the solution to our requirement.



Thanks & Regards,

Susanto Paul