SQL Server에서 여러 행을 효율적으로 Join하여 단일 구분된 필드로 변환하는 방법

데이터베이스 관리에서, 특히 SQL Server와 같은 관계형 데이터베이스를 다룰 때, 보다 나은 가독성과 간단한 처리를 위해 여러 행을 단일 필드로 결합해야 하는 상황을 자주 경험할 수 있습니다. 이 블로그 포스트에서는 SQL Server 기능을 사용하여 이 문제를 해결하는 방법을 다루며, 원하는 결과와 다양한 SQL Server 버전에 대한 효율적인 솔루션을 제시합니다.

문제 설명

Vehicles와 Locations라는 두 개의 테이블이 있다고 가정해 보겠습니다. 이 테이블들이 어떻게 생겼는지 간단한 예를 살펴봅시다:

Vehicles 테이블

Locations 테이블

목표는 각 차량의 이름과 그와 관련된 위치 목록을 함께 표시하는 방법으로 데이터를 추출하는 것입니다. 원하는 결과는 다음과 같아야 합니다:

복잡한 쿼리와 코딩을 통해 이 작업을 수행할 수 있지만, 우리는 과정을 단순화할 수 있는 솔루션이 필요합니다.

솔루션: SQL Server 함수를 사용하기

방법 1: FOR XML PATH 사용하기

SQL Server 2005 이상을 사용하는 경우, 이러한 연결을 달성하는 가장 효과적인 방법 중 하나는 FOR XML PATH 명령입니다. 이를 구현하는 방법은 다음과 같습니다:

SELECT [VehicleID],
       [Name],
       (STUFF((SELECT ', ' + [City] AS [text()]
                FROM [Location]
                WHERE (VehicleID = Vehicle.VehicleID)
                FOR XML PATH('')), 1, 2, '')) AS Locations
FROM [Vehicle]

설명:

• 내부 SELECT 문은 각 VehicleID에 대한 위치를 검색하고 이들을 단일 문자열로 연결합니다.
• FOR XML PATH('')는 하위 쿼리에서 반환된 여러 행으로부터 단일 XML 문자열을 생성할 수 있게 해줍니다.
• STUFF는 연결된 문자열에서 선행 쉼표와 공백을 제거하는 데 사용됩니다.

방법 2: STRING_AGG 사용하기 (SQL Server 2017 이상)

SQL Server 2017 이상을 사용하는 경우, STRING_AGG 함수를 사용하면 더욱 간단하게 이 작업을 수행할 수 있습니다. 방법은 다음과 같습니다:

SELECT [VehicleID],
       [Name],
       (SELECT STRING_AGG([City], ', ')
        FROM [Location]
        WHERE VehicleID = V.VehicleID) AS Locations
FROM [Vehicle] V

설명:

• STRING_AGG는 두 개의 인수를 취합니다: 집계할 열과 항목을 구분하는 구분자입니다. 이전 방법보다 훨씬 명확하고 효율적입니다.

결론

SQL Server에서 여러 행을 단일 구분된 필드로 결합하면 데이터의 가독성과 접근성이 대폭 향상될 수 있습니다. 구버전을 사용해서 FOR XML PATH를 이용하든 최신 버전에서 STRING_AGG를 사용할 수 있든, SQL Server는 데이터를 효율적으로 관리할 수 있는 도구를 제공합니다.

이러한 기술을 이해함으로써 SQL 쿼리를 단순화하여 읽기 쉽고 유지보수 및 수정이 용이하도록 만들 수 있습니다. 항상 SQL Server의 버전에 가장 적합한 방법과 특정 요구에 맞는 방법을 선택하세요.

이 방법들에 대해 질문이 있거나 추가 설명이 필요하다면 아래 댓글로 자유롭게 문의해 주세요!