LEVEL in Oracle SQL

🔹 LEVEL in Oracle SQL

The LEVEL pseudocolumn in Oracle is primarily used with hierarchical queries and CONNECT BY to generate sequences, recursive hierarchies, and tree structures.

1️⃣ Basic Usage of LEVEL to Generate a Sequence of Numbers

One of the simplest uses of LEVEL is to generate a series of numbers without needing a table.

SELECT LEVEL AS num
FROM DUAL
CONNECT BY LEVEL <= 10;

🔹 Output:

NUM
---
1
2
3
4
5
6
7
8
9
10

📌 Use Case: When you need a quick sequence of numbers for testing or looping.

2️⃣ Generating a Date Series

You can use LEVEL to generate a range of dates dynamically.

SELECT TRUNC(SYSDATE) + LEVEL - 1 AS date_series
FROM DUAL
CONNECT BY LEVEL <= 7;

🔹 Output:

DATE_SERIES
-----------
13-FEB-2025
14-FEB-2025
15-FEB-2025
16-FEB-2025
17-FEB-2025
18-FEB-2025
19-FEB-2025

📌 Use Case: Create a list of future dates dynamically.

3️⃣ Creating a Hierarchical Employee Tree

The LEVEL pseudocolumn is often used in hierarchical queries to represent parent-child relationships.

SELECT LEVEL, emp_id, emp_name, manager_id
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id;

🔹 Explanation:

START WITH manager_id IS NULL → Finds the top-level manager.
CONNECT BY PRIOR emp_id = manager_id → Builds a tree hierarchy.

📌 Use Case: Retrieve a company's employee hierarchy.

4️⃣ Creating an Organizational Chart with Indentation

SELECT LPAD(' ', LEVEL * 3) || emp_name AS employee_hierarchy
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id;

🔹 Output (Indented to show hierarchy):

CEO
   Manager A
      Employee 1
      Employee 2
   Manager B
      Employee 3

📌 Use Case: Display a hierarchical structure with proper indentation.

5️⃣ Creating a Fibonacci Series Using LEVEL

SELECT LEVEL, 
       ROUND((POWER((1 + SQRT(5)) / 2, LEVEL) - POWER((1 - SQRT(5)) / 2, LEVEL)) / SQRT(5)) AS fibonacci_number
FROM DUAL
CONNECT BY LEVEL <= 10;

🔹 Output:

LEVEL  FIBONACCI_NUMBER
-----  ----------------
1      1
2      1
3      2
4      3
5      5
6      8
7      13
8      21
9      34
10     55

📌 Use Case: Generate a Fibonacci series without using PL/SQL loops.

6️⃣ Duplicating Rows Using LEVEL

If you want to repeat each row multiple times, use LEVEL.

SELECT emp_name, LEVEL AS instance
FROM employees
CONNECT BY LEVEL <= 3;

📌 Use Case: Generate dummy test data.

7️⃣ Splitting a Comma-Separated String into Rows

If you have a column with comma-separated values and want to convert them into rows:

WITH data AS (
    SELECT 'Apple,Banana,Cherry' AS csv_data FROM DUAL
)
SELECT LEVEL, REGEXP_SUBSTR(csv_data, '[^,]+', 1, LEVEL) AS fruit
FROM data
CONNECT BY LEVEL <= REGEXP_COUNT(csv_data, ',') + 1;

🔹 Output:

LEVEL  FRUIT
-----  ------
1      Apple
2      Banana
3      Cherry

📌 Use Case: Normalize comma-separated values into rows.

8️⃣ Creating a Pivot Table Using LEVEL

If you need to transform rows into columns, LEVEL can be helpful:

SELECT MAX(CASE WHEN LEVEL = 1 THEN category END) AS col1,
       MAX(CASE WHEN LEVEL = 2 THEN category END) AS col2,
       MAX(CASE WHEN LEVEL = 3 THEN category END) AS col3
FROM (SELECT 'Electronics' AS category FROM DUAL UNION ALL
      SELECT 'Clothing' FROM DUAL UNION ALL
      SELECT 'Books' FROM DUAL)
CONNECT BY LEVEL <= 3;

🔹 Output:

COL1        COL2       COL3
----------  ---------  ------
Electronics Clothing  Books

📌 Use Case: Convert rows to columns dynamically.

🔹 Summary of LEVEL Use Cases

Use Case	Example
Generating a Number Sequence	`SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 10;`
Generating a Date Series	`SELECT SYSDATE + LEVEL FROM DUAL CONNECT BY LEVEL <= 7;`
Building Employee Hierarchies	`START WITH manager_id IS NULL CONNECT BY PRIOR emp_id = manager_id;`
Creating Indented Org Charts	`LPAD(' ', LEVEL * 3)
Generating Fibonacci Series	Formula with `POWER()` and `SQRT(5)`
Duplicating Rows	`CONNECT BY LEVEL <= 3;`
Splitting CSV Values into Rows	`REGEXP_SUBSTR(csv_data, '[^,]+', 1, LEVEL)`
Creating Pivot Tables	`CASE WHEN LEVEL = 1 THEN category END`

LEVEL in Oracle SQL