Oracle List Aggregate (LISTAGG) Function and Enhancements

Introduction

• LISTAGG Function: An aggregate function used in Oracle to convert rows of values into a comma-separated string.
• Versions Covered: Oracle 11g, 12c, and 19c.

Oracle 11g

Basic Functionality

• Syntax: LISTAGG(column_name, delimiter) WITHIN GROUP (ORDER BY column_name ASC|DESC)
• Converts column values into a comma-separated list.
• Optional delimiter: Can use any special character; default is null (no delimiter).
• WITHIN GROUP clause: Specifies sorting order for the values being aggregated.
• Example: Convert department-wise employee names into a comma-separated string.

Limitations in 11g

• Distinct Keyword: Cannot use DISTINCT inside LISTAGG.
  • Workaround: Use a subquery to remove duplicates before applying LISTAGG.
• String Length Limitation: Cannot concatenate strings longer than 4000 characters.
  • Error: Result of string concatenation is too long.
  • Workaround: Limit the number of concatenated strings to under 4000 characters.

Oracle 12c Enhancements

Addressed Limitations

• Overflow Management: New functionality to handle concatenation overflow.
• Syntax Enhancement: LISTAGG(column_name, delimiter ON OVERFLOW TRUNCATE)
  • ON OVERFLOW TRUNCATE: Truncates the string when it exceeds 4000 characters and appends an indicator (default ...).
  • Custom Truncation Indicator: Replace the default indicator with custom text.
  • With or Without Count: Optionally display the count of truncated elements.

Syntax Differences

• 11g vs 12c: Introduction of ON OVERFLOW TRUNCATE clause in 12c.

Oracle 19c Enhancements

New Features

• Distinct Keyword Inclusion: DISTINCT can now be used directly in LISTAGG.
• WITHIN GROUP Clause: No longer mandatory in 19c.
  • Default sorting order is ascending if omitted.

Syntax Overview

• 12c vs 19c:
  • 19c includes DISTINCT, and WITHIN GROUP is optional.

Practical Example

• Convert department-wise employee names into a distinct, comma-separated list without duplicates.
• Demonstrates non-mandatory WITHIN GROUP clause.

Summary

• LISTAGG Functionality Evolution: Enhancements from managing overflow to simplifying syntax and adding DISTINCT capability.
• Continued Limitations: Addressed in newer versions like 12c and 19c, offering more flexibility.

Conclusion

• Further Learning: Watch more videos for Oracle SQL features, interview questions, and performance tuning.
• Engagement: Like, subscribe, and enable notifications for updates.