복붙노트

[SQL] 쉼표로 구분 된 데이터 열을 조인

SQL

쉼표로 구분 된 데이터 열을 조인

내 표는 다음과 같습니다

col1    col2
 C1     john
 C2     alex
 C3     piers
 C4     sara

테이블이 너무 :

col1    col2
 R1     C1,C2,C4
 R2     C3,C4
 R3     C1,C4

이 결과하는 방법?

col1      col2
 R1       john,alex,sara
 R2       piers,sara
 R3       john,sara

저를 도와주세요?

해결법

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

    1.이상적으로, 당신의 가장 좋은 방법은 쉼표로 구분 된 목록을 저장되지 않도록 표 2를 정상화하는 것입니다.

    이상적으로, 당신의 가장 좋은 방법은 쉼표로 구분 된 목록을 저장되지 않도록 표 2를 정상화하는 것입니다.

    이 데이터는 표준화가되면 당신은 데이터를 쉽게 조회 할 수 있습니다. 새로운 테이블 구조는 다음과 유사 할 수 있습니다 :

    CREATE TABLE T1
(
  [col1] varchar(2), 
  [col2] varchar(5),
  constraint pk1_t1 primary key (col1)
);

INSERT INTO T1
    ([col1], [col2])
VALUES
    ('C1', 'john'),
    ('C2', 'alex'),
    ('C3', 'piers'),
    ('C4', 'sara')
;

CREATE TABLE T2
(
  [col1] varchar(2), 
  [col2] varchar(2),
  constraint pk1_t2 primary key (col1, col2),
  constraint fk1_col2 foreign key (col2) references t1 (col1)
);

INSERT INTO T2
    ([col1], [col2])
VALUES
    ('R1', 'C1'),
    ('R1', 'C2'),
    ('R1', 'C4'),
    ('R2', 'C3'),
    ('R2', 'C4'),
    ('R3', 'C1'),
    ('R3', 'C4')
;

    당신은 테이블을 조인하여 데이터를 조회하는 테이블을 정규화하면 훨씬 쉽게 만들 것입니다 :

    select t2.col1, t1.col2
from t2
inner join t1
  on t2.col2 = t1.col1

    참조 데모

    쉼표로 구분 된 목록으로 데이터를 표시하기를 원한다면 그런 다음 XML 경로와 물건 사용할 수 있습니다 :

    select distinct t2.col1, 
  STUFF(
         (SELECT distinct ', ' + t1.col2
          FROM t1
          inner join t2 t
            on t1.col1 = t.col2
          where t2.col1 = t.col1
          FOR XML PATH ('')), 1, 1, '') col2
from t2;

    데모를 참조하십시오.

    당신이 데이터를 정상화 할 수없는 경우에, 당신이 할 수있는 몇 가지가있다.

    첫째, 당신은에 가입 할 수있는 행으로 목록에 저장되어있는 데이터를 변환하는 분할 기능을 만들 수 있습니다. 분할 기능이 유사 할 것입니다 :

    CREATE FUNCTION [dbo].[Split](@String varchar(MAX), @Delimiter char(1))       
returns @temptable TABLE (items varchar(MAX))       
as       
begin      
    declare @idx int       
    declare @slice varchar(8000)       

    select @idx = 1       
        if len(@String)<1 or @String is null  return       

    while @idx!= 0       
    begin       
        set @idx = charindex(@Delimiter,@String)       
        if @idx!=0       
            set @slice = left(@String,@idx - 1)       
        else       
            set @slice = @String       

        if(len(@slice)>0)  
            insert into @temptable(Items) values(@slice)       

        set @String = right(@String,len(@String) - @idx)       
        if len(@String) = 0 break       
    end   
return 
end;

    당신이 분할을 사용하는 경우, 기능 당신이 중 하나를 여러 행의 데이터를 남길 수 있습니다 또는 당신은 다시 쉼표로 구분 된 목록에 값을 연결할 수 있습니다 :

    ;with cte as
(
  select c.col1, t1.col2
  from t1
  inner join 
  (
    select t2.col1, i.items col2
    from t2
    cross apply dbo.split(t2.col2, ',') i
  ) c
    on t1.col1 = c.col2
) 
select distinct c.col1, 
  STUFF(
         (SELECT distinct ', ' + c1.col2
          FROM cte c1
          where c.col1 = c1.col1
          FOR XML PATH ('')), 1, 1, '') col2
from cte c

    데모를 참조하십시오.

    당신은 결과를 얻을 수있는 마지막 방법은 직접 XML 경로에 대해 적용하는 것입니다.

    select col1, 
(
  select ', '+t1.col2
  from t1
  where ','+t2.col2+',' like '%,'+cast(t1.col1 as varchar(10))+',%'
  for xml path(''), type
).value('substring(text()[1], 3)', 'varchar(max)') as col2
from t2;

    데모와 SQL 바이올린을 참조하십시오

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

    2.여기에 분할의 방법은 다음 CSV 목록을 얻기위한 표준 XML PATH 방법을 사용하여, 데이터 함수없이입니다 :

    여기에 분할의 방법은 다음 CSV 목록을 얻기위한 표준 XML PATH 방법을 사용하여, 데이터 함수없이입니다 :

    with CTE as
(
  select T2.col1
    , T1.col2
  from T2
    inner join T1 on charindex(',' + T1.col1 + ',', ',' + T2.col2 + ',') > 0
)
select T2.col1
  , col2 = stuff(
      (
        select ',' + CTE.col2
        from CTE
        where T2.col1 = CTE.col1
        for xml path('')
      )
      , 1
      , 1
      , ''
    )
from T2

    데모와 SQL 바이올린.

    이 질문에 다른 언급 한 바와 같이이 첫 번째 우선 순위 테이블 구조를 업데이트 조사해야하므로, 효율적인 방법으로 모든 종류의에 denormalised 이러한 종류의 데이터를 조회하기 어렵지만, 적어도이 의지는 당신이 필요로하는 결과를 얻을 수 있습니다 .

  3. ==============================

    3.당신이 오라클에서이 작업을 수행하고자한다면 우리는 listagg을 사용할 수 있습니다 쉽게이 작업을 수행 할 수 있습니다.

    당신이 오라클에서이 작업을 수행하고자한다면 우리는 listagg을 사용할 수 있습니다 쉽게이 작업을 수행 할 수 있습니다.

    물론 나는 SQL Server의 좋은 아니라고하지만 난 listagg은 SQL Server에서 가능한 동등한 가능한 검색과 나는 같은에 대한 기능 물건을 가지고 -이 옵션을 선택합니다

    물건을 사용 있도록 쿼리를 다음과 같이 시도 할 수 있습니다 -

    SELECT T2.Col1,
       Stuff((SELECT ',' + CAST(T1.Col2 AS VARCHAR(100))
               FROM T1
              WHERE T2.Col2 LIKE T1.Col1
                FOR Xml Path('')),
             1,
             1,
             '')
  FROM T2
  4. ==============================

    4.먼저 TBL2에 분할 COL2에 대한 테이블 값 함수를 작성.

    먼저 TBL2에 분할 COL2에 대한 테이블 값 함수를 작성.

    CREATE FUNCTION [dbo].[Split](@String varchar(100), @Delimiter char(1))       
returns @temptable TABLE (items VARCHAR(5))       
as       
begin       
    declare @idx int       
    declare @slice VARCHAR(5)

    select @idx = 1       
        if len(@String)<1 or @String is null  return       

    while @idx!= 0       
    begin       
        set @idx = charindex(@Delimiter,@String)       
        if @idx!=0       
            set @slice = left(@String,@idx - 1)       
        else       
            set @slice = @String       

        if(len(@slice)>0)  
            insert into @temptable(Items) values(@slice)       

        set @String = right(@String,len(@String) - @idx)       
        if len(@String) = 0 break       
    end   
return       
end  

Go

;WITH    SplitList
          AS ( SELECT   T2.Col1 ,
                        T1.Col2
               FROM     T2
                        CROSS APPLY dbo.Split(T2.Col2, ',') S
                        INNER JOIN T1 ON T1.Col1 = S.Items
             )
    SELECT  T2.Col1 ,
            STUFF(( SELECT  ', ' + SplitList.Col2
                    FROM    SplitList
                    WHERE   SplitList.Col1 = T2.Col1
                  FOR
                    XML PATH('')
                  ), 1, 2, '')
    FROM    T2
  5. ==============================

    5.이 작업은 표준 SQL로 해결 될 수 없습니다. 오라클에서 나는 이름-ID 문자열 (T2의 COL2)를 구문 분석하고 이름을 확인하는 저장 기능 (PL / SQL)를 작성합니다. 즉 거래-SQL에 가능하다면 알고 있지만, 비효율적 영광입니다하지 마십시오.

    이 작업은 표준 SQL로 해결 될 수 없습니다. 오라클에서 나는 이름-ID 문자열 (T2의 COL2)를 구문 분석하고 이름을 확인하는 저장 기능 (PL / SQL)를 작성합니다. 즉 거래-SQL에 가능하다면 알고 있지만, 비효율적 영광입니다하지 마십시오.

    T2는 심하게 설계하지 정규화 테이블입니다. 그것이 문제이다. 당신이 이름-ID (T2에서 COL 2) 당 하나 개의 라인을 가지고 그래서, 그것을 정상화 할 경우, 당신은 두 테이블의 조인 간단한와 이름의 목록을 얻을 수 있습니다. 당신은 SQL보다 다른 쓰기 뭔가가 필요 (쉼표로 구분) 원하는 출력 포맷을 생성하려면 - 어쩌면 저장 프로 시저 또는 뭔가 다른 그 결과 집합을 반복.

  6. ==============================

    6.당신은 나 같은 당신은 CTE의 특히 재귀 CTE의 물건과 XML 경로에 가정으로의 까다로운 경우 :

    당신은 나 같은 당신은 CTE의 특히 재귀 CTE의 물건과 XML 경로에 가정으로의 까다로운 경우 :

    DECLARE @T1 TABLE (
    col1 CHAR(2),
    col2 VARCHAR(10)
)
INSERT INTO @T1
VALUES  ('C1', 'john'),
        ('C2', 'alex'),
        ('C3', 'piers'),
        ('C4', 'sara');

DECLARE @T2 TABLE (
    col1 CHAR(2),
    col2 CHAR(100)
)
INSERT INTO @T2
VALUES  ('R1', 'C1,C2,C4'),
        ('R2', 'C3,C4'),
        ('R3', 'C1,C4');

WITH T2Sorted AS (
    SELECT col1, col2, RN = ROW_NUMBER() OVER (ORDER BY col1) FROM @T2
), CTERecursionOnT2 AS (
    SELECT RN, col1, col2, 0 AS PrevCharIndex, CHARINDEX(',', col2, 1) AS NextCharIndex FROM T2Sorted
    UNION ALL
    SELECT a.RN, a.col1, a.col2, b.NextCharIndex, CHARINDEX(',', a.col2, b.NextCharIndex + 1) 
    FROM T2Sorted a
    JOIN CTERecursionOnT2 b ON a.RN = b.RN
    WHERE b.NextCharIndex > 0
), CTEIndividualCol2Items AS (
    SELECT *, SUBSTRING(col2, PrevCharIndex + 1, CASE WHEN NextCharIndex = 0 THEN LEN(col2) ELSE NextCharIndex - 1 END - PrevCharIndex) AS itemCol2 
    FROM CTERecursionOnT2
), CTELookupT1 AS (
    SELECT a.col1, b.col2, RN = ROW_NUMBER() OVER (PARTITION BY a.col1 ORDER BY a.PrevCharIndex)
    FROM CTEIndividualCol2Items a
    JOIN @T1 b ON a.itemCol2 = b.col1
), CTERecursionOnLookupT1 AS (
    SELECT col1, CAST(col2 AS VARCHAR(MAX)) AS col2, RN
    FROM CTELookupT1 
    WHERE RN = 1

    UNION ALL

    SELECT a.col1, b.col2 + ',' + a.col2, a.RN
    FROM CTELookupT1 a
    JOIN CTERecursionOnLookupT1 b ON a.col1 = b.col1 AND a.RN = b.RN + 1
), CTEFinal AS (
    SELECT *, RNDesc = ROW_NUMBER() OVER (PARTITION BY col1 ORDER BY RN DESC)
    FROM CTERecursionOnLookupT1
)
SELECT col1, col2
FROM CTEFinal
WHERE RNDesc = 1
ORDER BY col1

    이미 합의 된 솔루션 즉 CTERecursionOnT2 제안했다으로 분명히 별도의 함수로 최초의 재귀 부분을 나눌 수 있고, 따라서 CTEIndividualCol2Items이 다른 분할 기능을 할 수있다, 따라서 (나뿐만 아니라 주문 ID를 포함 할 것), 그리고 :

    ;WITH CTEIndividualCol2Items AS (
    SELECT a.col1, b.value as itemCol2, b.id AS PrevCharIndex
    FROM @T2 a
    CROSS APPLY (
        SELECT id, items FROM dbo.Split(a.col2, ',')
    ) b
) ...

    당신은 기능을 분할 :

    CREATE FUNCTION dbo.Split(@String varchar(100), @Delimiter char(1))
RETURNS TABLE
AS
RETURN 
(
    WITH CTERecursion AS (
        SELECT id = 1, PrevCharIndex = 0, NextCharIndex = CHARINDEX(@Delimiter, @String, 1)
        UNION ALL
        SELECT id + 1, NextCharIndex, CHARINDEX(@Delimiter, @String, NextCharIndex + 1) FROM CTERecursion WHERE NextCharIndex > 0
    )
    SELECT Id, items = SUBSTRING(@String, PrevCharindex + 1, (CASE WHEN NextCharIndex = 0 THEN LEN(@String) ELSE NextCharIndex - 1 END) - PrevCharIndex)
    FROM CTERecursion
    WHERE @String > ''
)

    7. from https://stackoverflow.com/questions/16507239/join-comma-delimited-data-column by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] SQL을 사용하여 입력 한 시간의 합을 계산  (0) 2020.04.15
[SQL] SQL Server의 임시 테이블의 범위  (0) 2020.04.15
[SQL] 예외 : SQLException : 문자열 또는 이진 데이터는 잘립니다  (0) 2020.04.15
[SQL] PostgreSQL의 배제에 함께 인접한 / 중첩 항목 예방  (0) 2020.04.15
[SQL] 날짜 시간 데이터 유형에 VARCHAR 데이터 형식의 변환이 범위를 벗어난 값 초래  (0) 2020.04.15
,

티스토리툴바