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.
Input Data
Let’s start with the input data. Assume we have a table named seat_data with the following structure:
SQL
CREATE TABLE seat_data (
customer_id INT,
section VARCHAR(50),
"row" CHAR(1),
seat INT
);We’ll insert some sample data into this table:
SQL
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:
SQL
WITH seat_ranges AS (
SELECT
customer_id,
section,
"row",
MIN(seat) AS min_seat,
MAX(seat) AS max_seat
FROM (
SELECT
customer_id,
section,
"row",
seat,
seat - ROW_NUMBER() OVER (PARTITION BY
customer_id, section, "row" ORDER BY seat)
AS grp
FROM seat_data) sub
GROUP BY customer_id, section, "row", grp
),
formatted_ranges AS (
SELECT
customer_id,
section,
"row",
STRING_AGG(
CASE
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 (
SELECT
customer_id,
STRING_AGG(section + ' ' + "row" + ' ' + seat_range, '; ') AS seat_groups
FROM formatted_ranges
GROUP BY customer_id
)
SELECT * FROM combined_ranges;How the Query Works
- 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.
- 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.
- 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 |
Conclusion
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!