하위 쿼리에서 여러 행을 단일 구분 필드로 "연결"하는 SQL Server 함수를 만드는 방법은 무엇입니까?

하위 쿼리에서 여러 행을 단일 구분 필드로 "연결"하는 SQL Server 함수를 만드는 방법은 무엇입니까?

이 질문에는 이미 답변이 있습니다.

SQL Server에서 여러 행의 텍스트를 단일 텍스트 문자열로 연결하는 방법 (47개 답변)

닫힘6년 전에요.

예를 들어 다음과 같은 두 개의 테이블이 있다고 가정합니다.

VehicleID Name
1         Chuck
2         Larry

LocationID VehicleID City
1          1         New York
2          1         Seattle
3          1         Vancouver
4          2         Los Angeles
5          2         Houston

다음 결과를 반환하기 위한 쿼리를 작성하려고 합니다.

VehicleID Name    Locations
1         Chuck   New York, Seattle, Vancouver
2         Larry   Los Angeles, Houston

서버측 커서를 사용하여 이 작업을 수행할 수 있습니다.

DECLARE @VehicleID int
DECLARE @VehicleName varchar(100)
DECLARE @LocationCity varchar(100)
DECLARE @Locations varchar(4000)
DECLARE @Results TABLE
(
  VehicleID int
  Name varchar(100)
  Locations varchar(4000)
)

DECLARE VehiclesCursor CURSOR FOR
SELECT
  [VehicleID]
, [Name]
FROM [Vehicles]

OPEN VehiclesCursor

FETCH NEXT FROM VehiclesCursor INTO
  @VehicleID
, @VehicleName
WHILE @@FETCH_STATUS = 0
BEGIN

  SET @Locations = ''

  DECLARE LocationsCursor CURSOR FOR
  SELECT
    [City]
  FROM [Locations]
  WHERE [VehicleID] = @VehicleID

  OPEN LocationsCursor

  FETCH NEXT FROM LocationsCursor INTO
    @LocationCity
  WHILE @@FETCH_STATUS = 0
  BEGIN
    SET @Locations = @Locations + @LocationCity

    FETCH NEXT FROM LocationsCursor INTO
      @LocationCity
  END
  CLOSE LocationsCursor
  DEALLOCATE LocationsCursor

  INSERT INTO @Results (VehicleID, Name, Locations) SELECT @VehicleID, @Name, @Locations

END     
CLOSE VehiclesCursor
DEALLOCATE VehiclesCursor

SELECT * FROM @Results

그러나 보시다시피 많은 코드가 필요합니다.저는 다음과 같은 작업을 수행할 수 있는 일반적인 기능을 원합니다.

SELECT VehicleID
     , Name
     , JOIN(SELECT City FROM Locations WHERE VehicleID = Vehicles.VehicleID, ', ') AS Locations
FROM Vehicles

이게 가능합니까?아니면 비슷한 거?

SQL Server 2005를 사용하는 경우 FOR XML PATH 명령을 사용할 수 있습니다.

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

이 방법은 커서를 사용하는 것보다 훨씬 쉽고 잘 작동하는 것 같습니다.

갱신하다

새로운 버전의 SQL Server에서 이 방법을 계속 사용하는 사용자에게는 SQL Server 2017 이후 사용 가능한 방법을 사용하여 조금 더 쉽고 성능이 향상되는 다른 방법이 있습니다.

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

또한 다른 구분자를 두 번째 매개 변수로 지정할 수 있으므로 이전 방법보다 조금 더 유연합니다.

Matt의 코드에 의해 문자열 끝에 쉼표가 추가됩니다.Lance의 투고 링크에 표시된 바와 같이 COALESCE(또는 ISULL)를 사용하는 것도 같은 방법을 사용하지만 삭제할 쉼표는 남기지 않습니다.완전성을 위해 sqlteam.com의 Lance 링크에 있는 관련 코드를 다음에 나타냅니다.

DECLARE @EmployeeList varchar(100)
SELECT @EmployeeList = COALESCE(@EmployeeList + ', ', '') + 
    CAST(EmpUniqueID AS varchar(5))
FROM SalesCallsEmployees
WHERE SalCal_UniqueID = 1

하나의 쿼리 내에서 실행할 수 있는 방법이 있다고는 생각하지 않지만 임시 변수를 사용하여 다음과 같은 속임수를 사용할 수 있습니다.

declare @s varchar(max)
set @s = ''
select @s = @s + City + ',' from Locations

select @s

확실히 커서 위를 걷는 것보다 코드가 적고, 아마 더 효율적일 것이다.

「SQL FOR XML」입니다.
일반 테이블 표현

-- rank locations by incrementing lexicographical order
WITH RankedLocations AS (
  SELECT
    VehicleID,
    City,
    ROW_NUMBER() OVER (
        PARTITION BY VehicleID 
        ORDER BY City
    ) Rank
  FROM
    Locations
),
-- concatenate locations using a recursive query
-- (Common Table Expression)
Concatenations AS (
  -- for each vehicle, select the first location
  SELECT
    VehicleID,
    CONVERT(nvarchar(MAX), City) Cities,
    Rank
  FROM
    RankedLocations
  WHERE
    Rank = 1

  -- then incrementally concatenate with the next location
  -- this will return intermediate concatenations that will be 
  -- filtered out later on
  UNION ALL

  SELECT
    c.VehicleID,
    (c.Cities + ', ' + l.City) Cities,
    l.Rank
  FROM
    Concatenations c -- this is a recursion!
    INNER JOIN RankedLocations l ON
        l.VehicleID = c.VehicleID 
        AND l.Rank = c.Rank + 1
),
-- rank concatenation results by decrementing length 
-- (rank 1 will always be for the longest concatenation)
RankedConcatenations AS (
  SELECT
    VehicleID,
    Cities,
    ROW_NUMBER() OVER (
        PARTITION BY VehicleID 
        ORDER BY Rank DESC
    ) Rank
  FROM 
    Concatenations
)
-- main query
SELECT
  v.VehicleID,
  v.Name,
  c.Cities
FROM
  Vehicles v
  INNER JOIN RankedConcatenations c ON 
    c.VehicleID = v.VehicleID 
    AND c.Rank = 1

가 본 바로는 ★★★★★★★★★★★★★★★★★★★★★★★★★★.FOR XMLOP와 같이 다른 열(대부분의 경우)도 선택할 수 있는 유일한 방법입니다.「」를 사용합니다.COALESCE(@var...서른

업데이트: programmingsolutions.net를 통해 콤마를 삭제할 수 있습니다.선두 콤마로 만들고,STUFF다음과 같이 첫 번째 문자(선행 쉼표)를 빈 문자열로 대체할 수 있습니다.

stuff(
    (select ',' + Column 
     from Table
         inner where inner.Id = outer.Id 
     for xml path('')
), 1,1,'') as Values

SQL Server 2005의 경우

SELECT Stuff(
  (SELECT N', ' + Name FROM Names FOR XML PATH(''),TYPE)
  .value('text()[1]','nvarchar(max)'),1,2,N'')

---

SQL Server 2016에서

FOR JSON 구문을 사용할 수 있습니다.

예.

SELECT per.ID,
Emails = JSON_VALUE(
   REPLACE(
     (SELECT _ = em.Email FROM Email em WHERE em.Person = per.ID FOR JSON PATH)
    ,'"},{"_":"',', '),'$[0]._'
) 
FROM Person per

그리고 그 결과는

Id  Emails
1   abc@gmail.com
2   NULL
3   def@gmail.com, xyz@gmail.com

데이터에 잘못된 XML 문자가 포함된 경우에도 작동합니다.

데이터에 '', {":":"가 포함되어 있으면 "",{\"_\:"로 이스케이프되므로 '",{\":'는 안전합니다.\"

, 를 임의의 문자열 구분 기호로 바꿀 수 있습니다.

---

또한 SQL Server 2017에서는 Azure SQL 데이터베이스

새로운 STRING_AGG 함수를 사용할 수 있습니다.

다음 코드는 SQL Server 2000/2005/2008에서 사용할 수 있습니다.

CREATE FUNCTION fnConcatVehicleCities(@VehicleId SMALLINT)
RETURNS VARCHAR(1000) AS
BEGIN
  DECLARE @csvCities VARCHAR(1000)
  SELECT @csvCities = COALESCE(@csvCities + ', ', '') + COALESCE(City,'')
  FROM Vehicles 
  WHERE VehicleId = @VehicleId 
  return @csvCities
END

-- //Once the User defined function is created then run the below sql

SELECT VehicleID
     , dbo.fnConcatVehicleCities(VehicleId) AS Locations
FROM Vehicles
GROUP BY VehicleID

다음과 같은 기능을 만들어 해결책을 찾았습니다.

CREATE FUNCTION [dbo].[JoinTexts]
(
  @delimiter VARCHAR(20) ,
  @whereClause VARCHAR(1)
)
RETURNS VARCHAR(MAX)
AS 
BEGIN
    DECLARE @Texts VARCHAR(MAX)

    SELECT  @Texts = COALESCE(@Texts + @delimiter, '') + T.Texto
    FROM    SomeTable AS T
    WHERE   T.SomeOtherColumn = @whereClause

    RETURN @Texts
END
GO

사용방법:

SELECT dbo.JoinTexts(' , ', 'Y')

문 선배의 답변이 통하지 않아서 그 답변에 수정을 가했어요.이게 도움이 됐으면 좋겠네요.SQL Server 2012 사용:

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

버전 메모: 이 솔루션에서는 호환성 수준이 90 이상으로 설정된 SQL Server 2005 이후를 사용해야 합니다.

테이블의 컬럼에서 가져온 문자열 값 세트를 연결하는 사용자 정의 집계 함수를 만드는 첫 번째 예에 대해서는 이 MSDN 문서를 참조하십시오.

부가된 콤마는 생략하고, 독자적인 애드혹 딜리미터를 사용할 수 있도록 하는 것이 좋습니다.

예 1의 C# 버전을 참조해 주세요.

change:  this.intermediateResult.Append(value.Value).Append(',');
    to:  this.intermediateResult.Append(value.Value);

그리고.

change:  output = this.intermediateResult.ToString(0, this.intermediateResult.Length - 1);
    to:  output = this.intermediateResult.ToString();

이 방법으로 커스텀 애그리게이트를 사용할 때 다음과 같이 독자적인 딜리미터를 사용하거나 전혀 사용하지 않도록 선택할 수 있습니다.

SELECT dbo.CONCATENATE(column1 + '|') from table1

메모: 집약에서 처리하려는 데이터의 양에 주의하십시오.수천 개의 행 또는 다수의 매우 큰 데이터 유형을 연결하려고 하면 이 표시될 수 있습니다.NET Framework 오류: "버퍼가 부족합니다."

다른 답안과 함께, 답을 읽는 사람은 차량 테이블을 알고 있어야 하며, 솔루션을 테스트하기 위한 차량 표와 데이터를 작성해야 합니다.

다음은 SQL Server "Information_Schema"를 사용하는 예입니다.Columns" 표.이 솔루션을 사용하면 테이블을 작성하거나 데이터를 추가할 필요가 없습니다.이 예에서는 데이터베이스의 모든 테이블에 대해 콤마로 구분된 열 이름 목록을 만듭니다.

SELECT
    Table_Name
    ,STUFF((
        SELECT ',' + Column_Name
        FROM INFORMATION_SCHEMA.Columns Columns
        WHERE Tables.Table_Name = Columns.Table_Name
        ORDER BY Column_Name
        FOR XML PATH ('')), 1, 1, ''
    )Columns
FROM INFORMATION_SCHEMA.Columns Tables
GROUP BY TABLE_NAME 

이 쿼리를 사용해 보세요.

SELECT v.VehicleId, v.Name, ll.LocationList
FROM Vehicles v 
LEFT JOIN 
    (SELECT 
     DISTINCT
        VehicleId,
        REPLACE(
            REPLACE(
                REPLACE(
                    (
                        SELECT City as c 
                        FROM Locations x 
                        WHERE x.VehicleID = l.VehicleID FOR XML PATH('')
                    ),    
                    '</c><c>',', '
                 ),
             '<c>',''
            ),
        '</c>', ''
        ) AS LocationList
    FROM Locations l
) ll ON ll.VehicleId = v.VehicleId

SQL Server 2005 를 실행하고 있는 경우는, 커스텀 CLR 집약 함수를 작성해, 이것을 처리할 수 있습니다.

C# 버전:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Text;
using Microsoft.SqlServer.Server;
[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.UserDefined,MaxByteSize=8000)]
public class CSV:IBinarySerialize
{
    private StringBuilder Result;
    public void Init() {
        this.Result = new StringBuilder();
    }

    public void Accumulate(SqlString Value) {
        if (Value.IsNull) return;
        this.Result.Append(Value.Value).Append(",");
    }
    public void Merge(CSV Group) {
        this.Result.Append(Group.Result);
    }
    public SqlString Terminate() {
        return new SqlString(this.Result.ToString());
    }
    public void Read(System.IO.BinaryReader r) {
        this.Result = new StringBuilder(r.ReadString());
    }
    public void Write(System.IO.BinaryWriter w) {
        w.Write(this.Result.ToString());
    }
}

언급URL : https://stackoverflow.com/questions/6899/how-to-create-a-sql-server-function-to-join-multiple-rows-from-a-subquery-into