[SQL] 어떻게 2005 SQL Server를 사용하여 별도의 행에 쉼표로 구분 된 값을 확장합니까?

어떻게 2005 SQL Server를 사용하여 별도의 행에 쉼표로 구분 된 값을 확장합니까?

나는 모습이 좋아하는 테이블이 있습니다 :

ProductId, Color
"1", "red, blue, green"
"2", null
"3", "purple, green"

그리고 나는이 그것을 확장하려는 :

ProductId, Color
1, red
1, blue
1, green
2, null
3, purple
3, green

가장 쉬운 방법은이 뭐죠 달성하기 위해? 그것은 PROC에서 루프없이 할 수 있습니까?

해결법

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

   1.나는 10 년 후 후에이 질문에 도착했다. SQL 서버 2016 STRING_SPLIT 기능을 추가했습니다. 그런를 사용하여이는 다음과 같이 쓸 수있다.

   나는 10 년 후 후에이 질문에 도착했다. SQL 서버 2016 STRING_SPLIT 기능을 추가했습니다. 그런를 사용하여이는 다음과 같이 쓸 수있다.

   declare @product table
   (
       ProductId int,
       Color     varchar(max)
   );
   insert into @product values (1, 'red, blue, green');
   insert into @product values (2, null);
   insert into @product values (3, 'purple, green');
   
   select
       p.ProductId as ProductId,
       ltrim(split_table.value) as Color
   from @product p
   outer apply string_split(p.Color, ',') as split_table;
   

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

   2.이 기능을 살펴보십시오. 나는 오라클의 분할 및 전치 데이터와 비슷한 트릭을 완료했습니다. 임시 테이블로 복호 값이 삽입 된 데이터에 대해 반복. 수도원의 것은 오라클이 명시 적으로 임시 테이블을 필요로하면서 MS, 당신은 즉시이 작업을 수행 할 것입니다.

   이 기능을 살펴보십시오. 나는 오라클의 분할 및 전치 데이터와 비슷한 트릭을 완료했습니다. 임시 테이블로 복호 값이 삽입 된 데이터에 대해 반복. 수도원의 것은 오라클이 명시 적으로 임시 테이블을 필요로하면서 MS, 당신은 즉시이 작업을 수행 할 것입니다.

   MS SQL 분할 기능 더 나은 분할 기능

   저자 편집 : 이것은 좋은 일했다. (분할 기능을 만든 후)이처럼 보였다 최종 코드 :

   select pv.productid, colortable.items as color
   from product p 
       cross apply split(p.color, ',') as colortable
   

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

   3.당신의 테이블을 기반으로 :

   당신의 테이블을 기반으로 :

   create table test_table
   (
        ProductId  int
       ,Color      varchar(100)
   )
   
   insert into test_table values (1, 'red, blue, green')
   insert into test_table values (2, null)
   insert into test_table values (3, 'purple, green')
   

   이 같은 새 테이블을 만들 :

   CREATE TABLE Numbers
   (
       Number  int   not null primary key
   )
   

   그 값 1-8000 정도를 포함하는 행을 갖는다.

   이것은 당신이 원하는 반환합니다 :

   편집하다 여기에 약간 @Christopher 클라인에서 위대한 대답에서 수정 훨씬 더 쿼리입니다 :

   내가 추가 한 'LTRIM () "색상 목록에서 공백이 있도록 처리 할 것 제대로"빨간색, 파란색, 녹색. " 그의 해결책은 "빨강, 파랑, 녹색,"공백이 필요하지 않습니다. 또한, 나는 master.dbo.spt_values를 사용하여 내 자신의 번호 테이블을 사용하지 선호,이 역시 하나 개의 파생 테이블의 제거를 할 수 있습니다.

   SELECT
       ProductId, LEFT(PartialColor, CHARINDEX(',', PartialColor + ',')-1) as SplitColor
       FROM (SELECT 
                 t.ProductId, LTRIM(SUBSTRING(t.Color, n.Number, 200)) AS PartialColor
                 FROM test_table             t
                     LEFT OUTER JOIN Numbers n ON n.Number<=LEN(t.Color) AND SUBSTRING(',' + t.Color, n.Number, 1) = ','
            ) t
   

   편집 END

   SELECT
       ProductId, Color --,number
       FROM (SELECT
                 ProductId
                     ,CASE
                          WHEN LEN(List2)>0 THEN LTRIM(RTRIM(SUBSTRING(List2, number+1, CHARINDEX(',', List2, number+1)-number - 1)))
                          ELSE NULL
                      END AS Color
                     ,Number
                 FROM (
                          SELECT ProductId,',' + Color + ',' AS List2
                              FROM test_table
                      ) AS dt
                     LEFT OUTER JOIN Numbers n ON (n.Number < LEN(dt.List2)) OR (n.Number=1 AND dt.List2 IS NULL)
                 WHERE SUBSTRING(List2, number, 1) = ',' OR List2 IS NULL
            ) dt2
       ORDER BY ProductId, Number, Color
   

   여기 내 결과 집합은 다음과 같습니다

   ProductId   Color
   ----------- --------------
   1           red
   1           blue
   1           green
   2           NULL
   3           purple
   3           green
   
   (6 row(s) affected)
   

   이는 당신이 원하는 순서입니다 ...

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

   4.당신은 어떤 추가 기능을 필요로 나던이 아웃을 시도 할 수 있습니다 :

   당신은 어떤 추가 기능을 필요로 나던이 아웃을 시도 할 수 있습니다 :

   declare @t table (col1 varchar(10), col2 varchar(200))
   insert @t
             select '1', 'red,blue,green'
   union all select '2', NULL
   union all select '3', 'green,purple'
   
   
   select col1, left(d, charindex(',', d + ',')-1) as e from (
       select *, substring(col2, number, 200) as d from @t col1 left join
           (select distinct number from master.dbo.spt_values where number between 1 and 200) col2
           on substring(',' + col2, number, 1) = ',') t
   

5. ==============================

   5.가능한 모든의 경우 데이터베이스를 수정합니다. 쉼표는 데이터베이스 세포의 목록이 시간 이상의 결함이 스키마 99 %를 나타냅니다 구분.

   가능한 모든의 경우 데이터베이스를 수정합니다. 쉼표는 데이터베이스 세포의 목록이 시간 이상의 결함이 스키마 99 %를 나타냅니다 구분.

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

   6.나는 이것에 대한 CLR 테이블 정의 함수를 만들 것입니다 :

   나는 이것에 대한 CLR 테이블 정의 함수를 만들 것입니다 :

   http://msdn.microsoft.com/en-us/library/ms254508(VS.80).aspx

   그 이유는 CLR 코드가 훨씬 더 떨어져 문자열 (전산 작업) 분석에 될 것입니다 및 SQL 서버 (설정 관리)에서 정말 좋은 것입니다 세트로 그 정보 등을 전달할 수있다.

   CLR이 함수는 파싱 된 값 (상기 ID 입력 값)에 기초하여 일련의 레코드를 반환한다.

   그런 다음 CROSS이 테이블의 각 요소에 적용 사용할 수 있습니다.

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

   7.그냥 XML로 열을 변환하고 쿼리합니다. 다음은 예입니다.

   그냥 XML로 열을 변환하고 쿼리합니다. 다음은 예입니다.

   select 
       a.value('.', 'varchar(42)') c
   from (select cast('<r><a>' + replace(@CSV, ',', '</a><a>') + '</a></r>' as xml) x) t1
   cross apply x.nodes('//r/a') t2(a)
   

from https://stackoverflow.com/questions/702968/how-do-i-expand-comma-separated-values-into-separate-rows-using-sql-server-2005 by cc-by-sa and MIT license