SQL에서 여러 열 데이터를 단일 문자열로 연결하는 모든 방법

SQL에서 여러 열 데이터를 단일 문자열로 연결하는 것은 보고서를 작성하거나 데이터를 표시할 때 매우 유용합니다. 이 기사에서는 주요 데이터베이스 시스템과 호환되는 다양한 방법을 소개하고, 그 장점과 사용 예를 자세히 설명합니다.

CONCAT 함수 사용

CONCAT 함수는 여러 열을 쉽게 연결할 수 있는 표준 방법입니다. 이 함수는 인수로 제공된 열이나 문자열을 연결하여 단일 문자열을 반환합니다. 많은 데이터베이스에서 지원됩니다.

사용 예

다음 예제는 고객의 성(last_name)과 이름(first_name)을 연결하여 전체 이름을 생성합니다.

SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM customers;

이 쿼리는 first_name과 last_name을 공백으로 연결하여 full_name이라는 별칭으로 표시합니다.

지원되는 데이터베이스

CONCAT 함수는 다음 데이터베이스 시스템에서 지원됩니다:

MySQL
PostgreSQL
SQL Server (2012 버전부터)
Oracle

CONCAT 함수는 간단하고 이해하기 쉬우며 많은 데이터베이스에서 일반적으로 사용되므로 가장 기본적인 방법으로 기억하기 좋습니다.

CONCAT_WS 함수 사용

CONCAT_WS 함수는 여러 열을 연결할 때 구분자를 지정할 수 있는 편리한 함수입니다. WS는 “With Separator”를 의미하며, 구분자와 연결할 열을 인수로 받습니다.

사용 예

다음 예제는 고객의 성(last_name), 이름(first_name) 및 중간 이름(middle_name)을 쉼표 구분자로 연결합니다.

SELECT CONCAT_WS(', ', first_name, middle_name, last_name) AS full_name
FROM customers;

이 쿼리는 first_name, middle_name 및 last_name을 쉼표와 공백으로 연결하여 full_name이라는 별칭으로 표시합니다.

지원되는 데이터베이스

CONCAT_WS 함수는 다음 데이터베이스 시스템에서 지원됩니다:

MySQL
PostgreSQL
SQL Server (2017 버전부터)
MariaDB

CONCAT_WS 함수는 열을 연결할 때 구분자를 명시적으로 지정할 수 있어 유연성을 높입니다. 특히 여러 필드를 일관된 형식으로 연결해야 할 때 유용합니다.

“||” 연산자 사용

“||” 연산자는 여러 열을 연결하는 간단한 방법으로, 많은 SQL 데이터베이스 시스템에서 지원됩니다. 이 연산자는 두 열이나 문자열을 연결하여 단일 문자열을 반환합니다.

사용 예

다음 예제는 고객의 성(last_name)과 이름(first_name)을 연결하여 전체 이름을 생성합니다.

SELECT first_name || ' ' || last_name AS full_name
FROM customers;

이 쿼리는 first_name과 last_name을 공백으로 연결하여 full_name이라는 별칭으로 표시합니다.

지원되는 데이터베이스

“||” 연산자는 다음 데이터베이스 시스템에서 지원됩니다:

PostgreSQL
Oracle
SQLite
DB2

“||” 연산자는 간단하고 가독성이 높으며 코딩이 최소화되어 기본 문자열 연결에 적합합니다. 그러나 일부 데이터베이스(MySQL, SQL Server)에서는 지원되지 않으므로 사용 전에 데이터베이스 사양을 확인해야 합니다.

+ 연산자 사용

+ 연산자는 주로 Microsoft SQL Server에서 열을 연결하는 데 사용됩니다. 이 연산자는 여러 열이나 문자열을 연결하여 단일 문자열을 생성합니다.

사용 예

다음 예제는 고객의 성(last_name)과 이름(first_name)을 연결하여 전체 이름을 생성합니다.

SELECT first_name + ' ' + last_name AS full_name
FROM customers;

이 쿼리는 first_name과 last_name을 공백으로 연결하여 full_name이라는 별칭으로 표시합니다.

지원되는 데이터베이스

+ 연산자는 다음 데이터베이스 시스템에서 문자열 연결에 사용할 수 있습니다:

SQL Server

이 방법은 간결하고 이해하기 쉽지만 SQL Server에만 국한되어 있어 다른 데이터베이스 시스템으로의 이식성이 제한됩니다. 또한 NULL 값이 포함된 경우 결과가 NULL이 되므로 NULL 값을 적절히 처리해야 합니다.

NULL 값 처리

NULL 값이 포함된 경우를 처리하려면 ISNULL 함수를 사용하여 NULL을 빈 문자열로 변환할 수 있습니다.

SELECT ISNULL(first_name, '') + ' ' + ISNULL(last_name, '') AS full_name
FROM customers;

이 쿼리는 first_name과 last_name이 NULL인 경우 이를 빈 문자열로 처리하여 NULL 값으로 인한 연결 오류를 방지합니다.

STRING_AGG 함수 사용 (PostgreSQL)

STRING_AGG 함수는 PostgreSQL에서 여러 열이나 행을 단일 문자열로 연결하는 데 사용되는 집계 함수입니다. 이 함수는 지정된 구분자로 열 값을 연결합니다.

사용 예

다음 예제는 여러 행의 고객 성(last_name)을 쉼표로 구분하여 연결합니다.

SELECT STRING_AGG(last_name, ', ') AS all_last_names
FROM customers;

이 쿼리는 고객 테이블의 모든 last_name 값을 쉼표와 공백으로 연결하여 all_last_names라는 별칭으로 표시합니다.

그룹별 사용 예

다음 예제는 부서별로 고객을 그룹화하고 각 부서의 구성원의 성을 연결합니다.

SELECT department, STRING_AGG(last_name, ', ') AS department_members
FROM customers
GROUP BY department;

이 쿼리는 부서별로 그룹화하고 last_name 값을 쉼표로 연결하여 department_members라는 별칭으로 표시합니다.

지원되는 데이터베이스

STRING_AGG 함수는 다음 데이터베이스 시스템에서 지원됩니다:

PostgreSQL
SQL Server (2017 버전부터)
MySQL (8.0 버전부터)

STRING_AGG 함수는 여러 행의 데이터를 구분자로 연결하는 데 매우 유용하며 집계 함수로 사용할 수 있어 보고서 작성 및 데이터 분석에 가치가 있습니다. 여러 값을 단일 필드로 결합해야 할 때 특히 효과적입니다.

GROUP_CONCAT 함수 사용 (MySQL)

GROUP_CONCAT 함수는 MySQL에서 여러 행의 데이터를 단일 문자열로 연결하는 데 사용되는 집계 함수입니다. 이 함수는 지정된 구분자로 열 값을 연결합니다.

사용 예

다음 예제는 고객의 성(last_name)을 쉼표로 구분하여 연결합니다.

SELECT GROUP_CONCAT(last_name SEPARATOR ', ') AS all_last_names
FROM customers;

이 쿼리는 고객 테이블의 모든 last_name 값을 쉼표와 공백으로 연결하여 all_last_names라는 별칭으로 표시합니다.

그룹별 사용 예

다음 예제는 부서별로 고객을 그룹화하고 각 부서의 구성원의 성을 연결합니다.

SELECT department, GROUP_CONCAT(last_name SEPARATOR ', ') AS department_members
FROM customers
GROUP BY department;

이 쿼리는 부서별로 그룹화하고 last_name 값을 쉼표로 연결하여 department_members라는 별칭으로 표시합니다.

지원되는 데이터베이스

GROUP_CONCAT 함수는 다음 데이터베이스 시스템에서 지원됩니다:

MySQL
MariaDB

GROUP_CONCAT 함수는 여러 행의 데이터를 구분자로 연결하는 데 매우 유용하며 MySQL 및 MariaDB에서 널리 사용됩니다. 집계 함수로 사용할 수 있어 보고서 작성 및 데이터 분석에 가치가 있습니다. 여러 값을 단일 필드로 결합해야 할 때 특히 효과적입니다.

STUFF 함수 사용 (SQL Server)

STUFF 함수는 SQL Server에서 문자열의 일부를 교체하거나 삽입하는 데 사용되지만 다른 함수와 결합하여 여러 열이나 행을 단일 문자열로 연결하는 데도 사용할 수 있습니다. 주로 FOR XML PATH 절과 함께 사용됩니다.

사용 예

다음 예제는 여러 행의 고객 성(last_name)을 쉼표로 구분하여 연결합니다.

SELECT STUFF(
    (SELECT ', ' + last_name
     FROM customers
     FOR XML PATH('')),
    1, 2, '') AS all_last_names;

이 쿼리는 고객 테이블의 모든 last_name 값을 쉼표와 공백으로 연결하고 첫 번째 쉼표와 공백을 제거하여 all_last_names라는 별칭으로 표시합니다.

그룹별 사용 예

다음 예제는 부서별로 고객을 그룹화하고 각 부서의 구성원의 성을 연결합니다.

SELECT department, 
       STUFF(
           (SELECT ', ' + last_name
            FROM customers AS c2
            WHERE c2.department = c1.department
            FOR XML PATH('')),
           1, 2, '') AS department_members
FROM customers AS c1
GROUP BY department;

이 쿼리는 부서별로 그룹화하고 last_name 값을 쉼표로 연결하여 department_members라는 별칭으로 표시합니다.

지원되는 데이터베이스

STUFF 함수는 다음 데이터베이스 시스템에서 지원됩니다:

SQL Server

STUFF 함수는 복잡한 문자열 작업을 수행하는 데 매우 강력하며 SQL Server에서 여러 행의 데이터를 단일 문자열로 결합하는 데 특히 유용합니다. FOR XML PATH 절과 결합하여 유연한 데이터 연결이 가능합니다.

사용자 정의 함수 사용

여러 열이나 행을 연결하기 위해 사용자 정의 함수를 생성하면 특정 요구 사항을 충족하는 유연성을 제공합니다. 각 데이터베이스 시스템은 사용자 정의 함수를 생성하는 방법을 제공하여 사용자 정의 연결 논리를 구현할 수 있습니다.

SQL Server에서의 사용자 정의 함수 예

다음 예제는 SQL Server에서 여러 열을 연결하기 위한 사용자 정의 함수를 생성하는 방법을 보여줍니다.

CREATE FUNCTION dbo.ConcatColumns (@first_name NVARCHAR(MAX), @last_name NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS
BEGIN
    RETURN @first_name + ' ' + @last_name
END;

이 함수는 first_name과 last_name을 공백으로 연결하여 단일 문자열을 반환합니다.

사용 예:

SELECT dbo.ConcatColumns(first_name, last_name) AS full_name
FROM customers;

PostgreSQL에서의 사용자 정의 함수 예

다음 예제는 PostgreSQL에서 여러 열을 연결하기 위한 사용자 정의 함수를 생성하는 방법을 보여줍니다.

CREATE OR REPLACE FUNCTION concat_columns(first_name TEXT, last_name TEXT)
RETURNS TEXT AS $$
BEGIN
    RETURN first_name || ' ' || last_name;
END;
$$ LANGUAGE plpgsql;

이 함수는 first_name과 last_name을 공백으로 연결하여 단일 문자열을 반환합니다.

사용 예:

SELECT concat_columns(first_name, last_name) AS full_name
FROM customers;

사용자 정의 함수 사용의 장점

재사용성: 한 번 생성하면 여러 쿼리에서 재사용할 수 있습니다.
유지보수성: 연결 논리를 함수로 통합하여 함수를 수정하는 것만으로 변경할 수 있습니다.
맞춤화: 특정 형식이나 조건을 쉽게 추가할 수 있습니다.

사용자 정의 함수는 복잡한 연결 논리를 수행하거나 특정 비즈니스 규칙을 준수하는 데 매우 편리합니다. 각 데이터베이스 시스템의 다양한 구문을 이해하고 최상의 방법을 선택하면 효율적인 데이터 조작이 가능합니다.

방법 비교 및 선택

여러 열 데이터를 단일 문자열로 연결하는 방법은 데이터베이스와 목적에 따라 선택해야 합니다. 아래는 주요 방법들의 비교와 선택에 대한 팁입니다.

CONCAT 함수

장점: 사용하기 쉽고 대부분의 데이터베이스에서 지원됩니다.
단점: 구분자를 지정할 수 없습니다.
적합한 경우: 간단한 문자열 연결.

CONCAT_WS 함수

장점: 구분자를 지정할 수 있으며 사용하기 쉽습니다.
단점: 일부 오래된 데이터베이스 버전에서는 지원되지 않습니다.
적합한 경우: 구분자가 있는 연결.

“||” 연산자

장점: 간단하고 직관적인 구문.
단점: MySQL 및 SQL Server에서는 지원되지 않습니다.
적합한 경우: PostgreSQL, Oracle, SQLite 등에서 기본 연결.

+ 연산자

장점: 간단하고 읽기 쉬움.
단점: SQL Server에만 국한됨. NULL 값을 처리해야 함.
적합한 경우: SQL Server에서 기본 연결.

STRING_AGG 함수

장점: 여러 행을 단일 문자열로 집계할 수 있음.
단점: 지원되는 데이터베이스가 제한적임.
적합한 경우: 여러 행을 단일 문자열로 집계.

GROUP_CONCAT 함수

장점: 여러 행을 단일 문자열로 집계할 수 있음.
단점: MySQL 및 MariaDB에만 국한됨.
적합한 경우: MySQL 또는 MariaDB에서 여러 행을 단일 문자열로 집계.

STUFF 함수

장점: 복잡한 문자열 작업 가능.
단점: SQL Server에만 국한되며 구문이 복잡함.
적합한 경우: SQL Server에서 고급 문자열 작업.

사용자 정의 함수

장점: 재사용 가능하며 맞춤화가 쉬움.
단점: 생성하는 데 시간이 걸림.
적합한 경우: 특정 연결 논리나 비즈니스 규칙 적용.

선택 시 고려사항

데이터베이스 유형: 사용 중인 데이터베이스의 지원 상태를 확인합니다.
연결 복잡성: 간단한 연결에는 간단한 함수를, 복잡한 연결에는 고급 또는 사용자 정의 함수를 사용합니다.
NULL 값 처리: NULL 값이 포함된 경우 이를 처리하는 방법을 고려합니다.

각 방법의 특성을 이해하고 특정 사용 사례와 데이터베이스 속성에 따라 최적의 방법을 선택하는 것이 중요합니다.

결론

SQL에서 여러 열 데이터를 단일 문자열로 연결하는 다양한 방법이 있습니다. 주요 방법에는 CONCAT 함수, CONCAT_WS 함수, “||” 연산자, + 연산자, STRING_AGG 함수, GROUP_CONCAT 함수, STUFF 함수 및 사용자 정의 함수가 있습니다. 각 방법은 장단점이 있으며 데이터베이스와 특정 요구 사항에 따라 최적의 방법을 선택하는 것이 중요합니다.

CONCAT 함수나 “||” 연산자는 간단한 연결에 적합하며, + 연산자나 STUFF 함수는 SQL Server 작업에 특화되어 있습니다. STRING_AGG 함수나 GROUP_CONCAT 함수는 여러 행을 연결하는 데 적합합니다. 또한, 사용자 정의 함수는 특정 비즈니스 논리에 따라 연결할 때 유용합니다. 적절한 방법을 선택함으로써 효율적이고 가독성 높은 SQL 쿼리를 작성하고 효과적인 데이터베이스 작업을 수행할 수 있습니다.