Using CONCAT in SQL to Combine Text from Multiple Columns

1. Purpose of CONCAT in SQL

In SQL, the CONCAT function combines two or more text strings into a single string.

It is conceptually similar to spreadsheet functions like CONCATENATE, but operates at the database level and can combine values across columns and tables within large datasets.

This is especially useful when:

• Creating readable identifiers
• Building labels
• Combining attributes into a new feature
• Preparing grouped outputs for aggregation

2. Text Strings in SQL

A text string is a sequence of characters stored in a text-type column (e.g., STRING, VARCHAR).

Examples:

• Station names
• User types
• Categories
• Addresses

String functions allow analysts to manipulate and combine these values.

3. The CONCAT Function

Basic Syntax

CONCAT(string1, string2, ..., stringN)

CONCAT(string1, string2, ..., stringN)

It returns a single combined string.

Example:

SELECT CONCAT('Start: ', start_station, ' → End: ', end_station) AS route
FROM trips;

SELECT CONCAT('Start: ', start_station, ' → End: ', end_station) AS route
FROM trips;

This creates a readable route label.

4. Example Context: Citi Bike Open Data

Open data refers to publicly accessible datasets that are free to use and share.

The Citi Bike dataset includes:

• User type (Subscriber or Customer)
• Start station
• End station
• Trip duration
• Trip timestamps

We want to analyze:

• Most popular routes
• Differences between user types
• Average trip duration by route

5. Constructing a Route Label with CONCAT

To analyze routes, we create a new column that combines:

• Start station name
• End station name

Example:

CONCAT(start_station_name, ' to ', end_station_name) AS route

CONCAT(start_station_name, ' to ', end_station_name) AS route

This produces values like:

Central Park to Times Square

Central Park to Times Square

Creating this derived column simplifies grouping and counting.

6. Full Query Breakdown

Step 1: Select User Type

SELECT user_type,

SELECT user_type,

This allows comparison across customer categories.

Step 2: Create Route Column

CONCAT(start_station_name, ' to ', end_station_name) AS route,

CONCAT(start_station_name, ' to ', end_station_name) AS route,

Generates readable route names.

Step 3: Count Trips

COUNT(*) AS num_trips,

COUNT(*) AS num_trips,

• COUNT(*) counts rows.
• Each row represents one trip.
• Alias improves readability.

Step 4: Compute Average Duration

To calculate average trip duration:

ROUND(AVG(CAST(trip_duration AS INT64)), 2) AS duration

ROUND(AVG(CAST(trip_duration AS INT64)), 2) AS duration

Explanation:

• CAST(... AS INT64) converts data type if necessary.
• AVG() calculates average.
• ROUND(..., 2) rounds to two decimal places.

BigQuery uses INT64 for 64-bit integers.

7. FROM Clause

Specify data source:

FROM dataset.trips

FROM dataset.trips

SQL must know where the columns originate.

8. GROUP BY Clause

When using aggregate functions (COUNT, AVG), SQL requires grouping of non-aggregated fields.

GROUP BY user_type, start_station_name, end_station_name

GROUP BY user_type, start_station_name, end_station_name

This ensures:

• One summary row per route per user type.

9. ORDER BY Clause

To find most common routes:

ORDER BY num_trips DESC

ORDER BY num_trips DESC

Descending order shows highest counts first.

10. LIMIT Clause

To restrict output:

LIMIT 10

LIMIT 10

Returns only top 10 routes.

11. Complete Example Query

SELECT 
  user_type,
  CONCAT(start_station_name, ' to ', end_station_name) AS route,
  COUNT(*) AS num_trips,
  ROUND(AVG(CAST(trip_duration AS INT64)), 2) AS duration
FROM dataset.trips
GROUP BY user_type, start_station_name, end_station_name
ORDER BY num_trips DESC
LIMIT 10;

SELECT 
  user_type,
  CONCAT(start_station_name, ' to ', end_station_name) AS route,
  COUNT(*) AS num_trips,
  ROUND(AVG(CAST(trip_duration AS INT64)), 2) AS duration
FROM dataset.trips
GROUP BY user_type, start_station_name, end_station_name
ORDER BY num_trips DESC
LIMIT 10;

12. Analytical Interpretation

This query allows analysts to:

• Identify popular routes
• Compare behavior by user type
• Evaluate average trip length
• Inform operational decisions (e.g., bike redistribution)

Derived features like route improve clarity and analytical flexibility.

13. Why CONCAT Is Powerful

CONCAT enables:

• Creation of composite identifiers
• Easier grouping
• Clear labeling for dashboards
• Improved human readability

It transforms raw columns into meaningful structured categories.

14. Relationship to Other SQL Concepts

CONCAT is often used alongside:

• JOIN (to combine tables)
• GROUP BY (to aggregate)
• COUNT, AVG, SUM (for metrics)
• ORDER BY (for ranking)

String manipulation is a foundational skill in SQL analytics.

15. Summary

The SQL CONCAT function:

• Combines multiple text fields
• Creates readable composite strings
• Supports grouping and aggregation
• Enhances clarity in output tables

When combined with aggregation, filtering, and sorting, CONCAT enables structured and interpretable analysis across large-scale datasets.