Creating a SQL Query to Group and Format Seat Data

by | Database, SQL | 0 comments

At one point in my career, I needed to create invoices with dynamic data from a live event ticketing system which contained Broadway season subscription data.  Specifically, the invoices needed to show assigned seats in an easy to read format for the customer that didn’t take up a tremendous amount of room on the invoice.

In this blog post, we’ll walk through the process of creating a SQL query that groups and formats seat data by customer, section, and row. The goal is to generate a readable output that lists seat numbers in contiguous ranges, separated by commas for each section and row. Changes in section or row will be delimited by a semicolon.

If your system does not allow direct query of the database, you can achieve this by using reporting tools to extract the seat data in CSV format. Then, load this data into a separate database specifically for this purpose.

Depicts an invoice for a Broadway season subscription renewal.

Input Data

Let’s start with the input data. Assume we have a table named seat_data with the following structure:



CREATE TABLE seat_data (
customer_id INT,
section VARCHAR(50),
"row" CHAR(1),
seat INT

We’ll insert some sample data into this table:



INSERT INTO seat_data (customer_id, section, "row", seat) VALUES
(10357, 'ORCHESTRA CENTER', 'E', 23),
(10357, 'ORCHESTRA CENTER', 'E', 24),
(10357, 'ORCHESTRA CENTER', 'E', 25),
(10357, 'ORCHESTRA CENTER', 'E', 26),
(10357, 'ORCHESTRA CENTER', 'E', 30),
(10357, 'ORCHESTRA CENTER', 'E', 31),
(10357, 'ORCHESTRA CENTER', 'E', 32),
(10357, 'ORCHESTRA CENTER', 'E', 35),
(10357, 'ORCHESTRA RIGHT', 'E', 57),
(10357, 'ORCHESTRA CENTER', 'E', 58),
(10357, 'ORCHESTRA CENTER', 'E', 60),
(29875, 'BALCONY CENTER', 'H', 5),
(29875, 'BALCONY CENTER', 'H', 6),
(29875, 'BALCONY CENTER', 'H', 8),
(29875, 'BALCONY CENTER', 'H', 9),
(29875, 'BALCONY CENTER', 'H', 10),
(29875, 'BALCONY CENTER', 'H', 17);

The SQL Query

To achieve the desired output, we need a query that groups contiguous seat numbers and formats them appropriately. Here is the SQL query that accomplishes this:



WITH seat_ranges AS (
MIN(seat) AS min_seat,
MAX(seat) AS max_seat
customer_id, section, "row" ORDER BY seat)
AS grp
FROM seat_data) sub
GROUP BY customer_id, section, "row", grp
formatted_ranges AS (
WHEN min_seat = max_seat THEN CAST(min_seat AS VARCHAR)
ELSE CAST(min_seat AS VARCHAR) + '-' + CAST(max_seat AS VARCHAR)
END, ', ') AS seat_range
FROM seat_ranges
GROUP BY customer_id, section, "row"
combined_ranges AS (
STRING_AGG(section + ' ' + "row" + ' ' + seat_range, '; ') AS seat_groups
FROM formatted_ranges
GROUP BY customer_id
SELECT * FROM combined_ranges;

How the Query Works

  1. seat_ranges CTE: This common table expression (CTE) calculates the minimum and maximum seat for contiguous seat numbers grouped by customer_id, section, and row. It uses the ROW_NUMBER() function to identify groups of contiguous seats.
  2. formatted_ranges CTE: This CTE formats the seat ranges, creating a string representation of each range. The STRING_AGG function concatenates the seat ranges with a comma separator.
  3. combined_ranges CTE: This CTE combines the section, row, and formatted seat ranges into a single string for each customer. The STRING_AGG function is used to concatenate these ranges, separated by commas.

Query Output

After running the query, we get the following output:

Customer Seats
10357 ORCHESTRA CENTER E 23-26, 30-32, 35, 58, 60; ORCHESTRA RIGHT E 57
29875 BALCONY CENTER H 5-6, 8-10, 17


This SQL query efficiently groups and formats seat data for each customer, making the output easy to read and understand. By using common table expressions (CTEs) and the STRING_AGG function, we can generate a consolidated view of season subscription seat allocations, which is particularly useful for event management systems and live event ticketing platforms.

Feel free to adapt this query to fit your specific database schema and requirements. Happy querying!