복붙노트

[SQL] 어떻게 하나의 컬럼 식별자 필드에 서브 쿼리에서 "가입"여러 행에 SQL 서버 기능을 만드는 방법? [복제]

SQL

어떻게 하나의 컬럼 식별자 필드에 서브 쿼리에서 "가입"여러 행에 SQL 서버 기능을 만드는 방법? [복제]

나는 다음과 같이 두 테이블이 있다고 가정, 설명하기 :

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

이게 가능해? 아니면 비슷한?

해결법

  1. ==============================

    1.당신은 SQL 서버 2005를 사용하는 경우 XML PATH 명령에 대해 사용할 수 있습니다.

    당신은 SQL 서버 2005를 사용하는 경우 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]

    그것은 커서를 사용하는 것보다 훨씬 쉽게, 그리고 꽤 잘 작동하는 것 같다.

  2. ==============================

    2.매트의 코드는 문자열의 끝에 추가 쉼표가 발생합니다 있습니다; 랜스의 게시물에 링크에서와 같이 (그 문제에 대한 또는 ISNULL) COALESCE을 사용하여 유사한 방법을 사용하지만 제거 할 수있는 여분의 쉼표로 당신을 떠나지 않습니다. 완벽을 위해, 여기 sqlteam.com에 랜스의 링크에서 관련 코드는 다음과 같습니다

    매트의 코드는 문자열의 끝에 추가 쉼표가 발생합니다 있습니다; 랜스의 게시물에 링크에서와 같이 (그 문제에 대한 또는 ISNULL) COALESCE을 사용하여 유사한 방법을 사용하지만 제거 할 수있는 여분의 쉼표로 당신을 떠나지 않습니다. 완벽을 위해, 여기 sqlteam.com에 랜스의 링크에서 관련 코드는 다음과 같습니다

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

    3.나는 거기에 하나 개의 쿼리 내에서 할 수있는 방법은,하지만 당신은 임시 변수와 같은 트릭을 재생할 수 있다고 생각하지 않습니다

    나는 거기에 하나 개의 쿼리 내에서 할 수있는 방법은,하지만 당신은 임시 변수와 같은 트릭을 재생할 수 있다고 생각하지 않습니다

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

select @s

    그것은 확실히 덜 커서 걷고보다는 코드, 그리고 아마도 더 효율적입니다.

  4. ==============================

    4.단일 SQL 쿼리에서, 대한의 XML 절을 사용하지 않고. 공통 테이블 식 재귀 적 결과를 연결하는 데 사용됩니다.

    단일 SQL 쿼리에서, 대한의 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
  5. ==============================

    5.당신은 또한 영업 이익은처럼 (내가 가장 것이라고 추측에는 요) 다른 열을 선택하려는 경우 (이전에 게시 된) 내가 FOR XML을 볼 수있는에서 것은 그것을 할 수있는 유일한 방법입니다. COALESCE (@var를 사용 ... 다른 컬럼의 포함을 허용하지 않습니다.

    당신은 또한 영업 이익은처럼 (내가 가장 것이라고 추측에는 요) 다른 열을 선택하려는 경우 (이전에 게시 된) 내가 FOR XML을 볼 수있는에서 것은 그것을 할 수있는 유일한 방법입니다. COALESCE (@var를 사용 ... 다른 컬럼의 포함을 허용하지 않습니다.

    최신 정보: programmingsolutions.net 덕분에 "후행"쉼표를 제거하는 방법이있다. 선두 쉼표로를 만들고 MSSQL의 물건 기능을 사용하여 아래로 빈 문자열의 첫 번째 문자 (선행 쉼표를) 대체 할 수있다 :

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

    6.

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

    당신은에 대한 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 문자가 포함 된 경우에도 데이터를 작동합니다

    ,이 _ \ ", {\} '으로 탈출 할 것'"}, { "": ": \" ' ""}, { ""'당신의 데이터가 포함 된 경우 때문에 안전하다 " '

    당신은 어떤 문자열 구분 기호 ','대체 할 수있다

    당신은 새로운 STRING_AGG 기능을 사용할 수 있습니다

  7. ==============================

    7.아래의 코드는 SQL 서버 2000/2005/2008을 위해 작동합니다

    아래의 코드는 SQL 서버 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
  8. ==============================

    8.나는 다음과 같은 기능을 작성하여 해결책을 발견했습니다 :

    나는 다음과 같은 기능을 작성하여 해결책을 발견했습니다 :

    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')
  9. ==============================

    9.나는 일에 그걸 얻기 위해 그 답을 일부 변경했다, 그래서 MUN의 대답은 나를 위해 작동하지 않았다. 이 사람을 도움이되기를 바랍니다. SQL 서버 2012을 사용 :

    나는 일에 그걸 얻기 위해 그 답을 일부 변경했다, 그래서 MUN의 대답은 나를 위해 작동하지 않았다. 이 사람을 도움이되기를 바랍니다. SQL 서버 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]
  10. ==============================

    10.VERSION 참고 :이 솔루션의 90 이상에 대한 호환성 수준 설정과 SQL Server 2005 또는 이상을 사용해야합니다.

    VERSION 참고 :이 솔루션의 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

    참고 : 집계에서 처리를 시도 데이터의 양에주의해야합니다. 당신이 행 또는 많은 매우 큰 데이터 유형의 CONCATENATE 수천하려고하면 당신은 내용의 .NET 프레임 워크 오류가 발생할 수 있습니다 "그가 버퍼 [t]가 부족합니다."

  11. ==============================

    11.다른 답변으로 대답을 읽는 사람은 차량 테이블을 인식하고 해결책을 테스트하는 차량 테이블과 데이터를 작성해야합니다.

    다른 답변으로 대답을 읽는 사람은 차량 테이블을 인식하고 해결책을 테스트하는 차량 테이블과 데이터를 작성해야합니다.

    다음은 SQL 서버 "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
  12. ==============================

    12.이 쿼리

    이 쿼리

    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
  13. ==============================

    13.당신은 SQL 서버 2005를 실행하는 경우, 당신은이 문제를 처리하기 위해 사용자 정의 CLR 집계 함수를 작성할 수 있습니다.

    당신은 SQL 서버 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());
    }
}

    14. from https://stackoverflow.com/questions/6899/how-to-create-a-sql-server-function-to-join-multiple-rows-from-a-subquery-into by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] 두 날짜 사이의 날짜의 목록을 가져옵니다  (0) 2020.03.08
[SQL] 쉼표에서 MySQL의 쿼리 찾는 값은 문자열을 분리  (0) 2020.03.08
[SQL] 오라클 SQL 동적 피벗  (0) 2020.03.08
[SQL] 왜 오라클은 NULL로 빈 문자열을 취급 9I는 무엇입니까?  (0) 2020.03.08
[SQL] 어느 / 빠른 최고? SELECT * 또는 SELECT 컬럼 1, colum2, 3 열, 등  (0) 2020.03.08
,

티스토리툴바