데이터가 아래와 같이 "," 로 분리가 된 데이터들이 들어있습니다.
여러건인 경우는 , 로 구분자를 두어서 저장을 해놨는데....
이를 모두 펼쳐야 하는 일이 발생을 하게 되었습니다.
',' 로 분리가 된 데이터들을 하나의 row 로 분리를 해서 보일 수 있도록 할 수 있을 까요?
DATA1 | DATA2 | DATA3 |
8853-G4K,8852-4YK | 99GP093,99C7740 | 42D0423,68Y8205 |
ML350-G4 | CN762100JN | 40T1566 |
9117-570,9117-570 | 6563AAD,6563ADD | 39J2779,12R9259 |
ML350-G4 | CN762100JN | 40T1566 |
9117-570,9117-570 | 65BC4AC,65BC4BC | 12R9259,12R9259 |
VNX5300 | CKM00123801942 | 5049197 |
9117-570 | 655EE6C | 39J0859 |
9117-570 | 655EE6C | 39J0859 |
9117-570 | 6569EBD | 03N6325 |
7042-CR6 | 063EBDC | 81Y9727 |
7042-CR6 | 063EBDC | 81Y9729 |
3588-F4A ,3588-F4A | 7896202 ,7896265 | LTO Drive,LTO Drive |
결과가
DATA1 | DATA2 | DATA3 |
8853-G4K | 99GP093 | 42D0423 |
8852-4YK | 99C7740 | 68Y8205 |
ML350-G4 | CN762100JN | 40T1566 |
9117-57 | 6563AAD | 39J2779 |
9117-570 | 6563ADD | 12R9259 |
ML350-G4 | CN762100JN | 40T1566 |
9117-570 | 65BC4AC | 12R9259 |
9117-570 | 65BC4BC | 12R9259 |
VNX5300 | CKM00123801942 | 5049197 |
9117-570 | 655EE6C | 39J0859 |
9117-570 | 655EE6C | 39J0859 |
9117-570 | 6569EBD | 03N6325 |
7042-CR6 | 063EBDC | 81Y9727 |
7042-CR6 | 063EBDC | 81Y9729 |
3588-F4A | 7896202 | LTO Drive |
3588-F4A | 7896265 | LTO Drive |
이렇게 나오게 하고 싶은데요...
Comment 6
-
건우아빠
2016.12.22 10:36
-
DOOLLY
2016.12.22 11:18
위의 게시글과 반대로 하려고 합니다.
분리자로 되어져 있는 것들을 새로운 row 로 만들려고...
Select SUBSTRING(a.DATA1,b.s,b.e-b.s) AS Split from
(Select * from TABLE ) a
CROSS APPLY ( SELECT number AS s, CHARINDEX(',',a.DATA1+',',number+1) AS e
FROM master..spt_values
WHERE number = CHARINDEX(',',','+a.DATA1,number ) ) B이런식의 답변이 있어서 해보려고 했는데... 필드 하나만... 되고... 3개의 필드 모두 적용을 하려니....
"," 로 구분이 되어져 있는게 하나의 셋트여서...
DATA1 DATA2 DATA3 에 있는 데이터가 상관이 없는게 아니라
Data1 에서 컴마로 구분된것과 DATA2, DATA3의 컴마가 구분이 된게 관련이 있습니다.
첫번째거는 첫번째 쌍.. 두번째꺼는 두번째꺼... 그렇게 구성이 되어져 있습니다.
그래서 각각 뽑은 다음에 정렬을 할 수는 없을 것 같습니다.
-
건우아빠
2016.12.22 15:41
원하시는 내용도 포함되어 있습니다.
답글에 보시면 링크 걸린 부분이 있습니다.
이부분을 참고 하시면 원하시는 결과를 만들어 내실수 있을것 같습니다.
-
건우아빠
2016.12.22 17:22
parsename 함수를 이용한겁니다. 구분자로 분리된 글이 4개 이하일때 이용가능
뒷에서부터가 1,2,3,4 로 보시면 됩니다.
5개 이상인건 링크건 내용의 답글의 링크를 보시면 민석님이 답글한 내용이 있습니다. 이걸 이용하시면 됩니다.
SQL 사용자 Tip & 강좌에 구분자로 검색하시면 여러 방법이 나옵니다. 이걸 숙지 하시면 이런류의 내용은 손쉽게 구현 가능 하실겁니다.
with res
as
(
select '8853-G4K,8852-4YK' DATA1,'99GP093,99C7740' DATA2,'42D0423,68Y8205' DATA3 union all
select 'ML350-G4' DATA1,'CN762100JN' DATA2,'40T1566' union all
select '9117-570,9117-570' DATA1,'6563AAD,6563ADD' DATA2,'39J2779,12R9259' union all
select 'ML350-G4' DATA1,' CN762100JN' DATA2,'40T1566' union all
select '9117-570,9117-570' DATA1,'65BC4AC,65BC4BC' DATA2,'12R9259,12R9259' union all
select 'VNX5300' DATA1,'CKM00123801942' DATA2,'5049197' union all
select '9117-570' DATA1,'655EE6C' DATA2,'39J0859' union all
select '9117-570' DATA1,'655EE6C' DATA2,'39J0859' union all
select '9117-570' DATA1,'6569EBD' DATA2,'03N6325' union all
select '7042-CR6' DATA1,'063EBDC' DATA2,'81Y9727' union all
select '7042-CR6' DATA1,'063EBDC' DATA2,'81Y9729' union all
select '3588-F4A ,3588-F4A' DATA1,'7896202 ,7896265' DATA2,'LTO Drive,LTO Drive'
)
select --* , LEN(a.DATA1) - LEN(replace(a.DATA1,',',''))
parsename(replace(a.DATA1,',','.') ,b.num ) DATA1
, parsename(replace(a.DATA2,',','.') ,b.num ) DATA2
, parsename(replace(a.DATA3,',','.') ,b.num ) DATA3
from res a
cross apply
(
select number + 1 num
from master.dbo.spt_values
where type = 'P'
and number <= LEN(a.DATA1) - LEN(replace(a.DATA1,',',''))
) b
-
건우아빠
2016.12.22 17:29
CREATE FUNCTION MssqlINSTR(@OriginalText VARCHAR(8000),@GubunText VARCHAR(100),@Pos INT)
RETURNS VARCHAR(8000)
BEGIN
DECLARE @CleanedText VARCHAR(8000)
DECLARE @ReplaceText VARCHAR(8000)
DECLARE @Minus int ,@I INT ,@TempPOS INT ,@NextTempPOS INT
SET @OriginalText = @GubunText + @OriginalText + @GubunText
SET @ReplaceText = REPLACE(@OriginalText ,@GubunText,'')
SET @Minus = len(@OriginalText) - len(@ReplaceText)
IF @Minus - 1 < @Pos
BEGIN
RETURN ''
END
IF @Pos < 1
BEGIN
RETURN ''
END
SET @TempPOS = 0
SET @I = 0
WHILE @Pos > @I
BEGIN
SET @TempPOS = CHARINDEX(@GubunText, @OriginalText , @TempPOS + 1 )
SET @NextTempPOS = CHARINDEX(@GubunText, @OriginalText , @TempPOS + 1 )
SET @I = @I + 1
END
RETURN SUBSTRING(@OriginalText ,@TempPOS + 1 ,@NextTempPOS - @TempPOS - 1)
END
함수이용
with res
as
(
select '8853-G4K,8852-4YK' DATA1,'99GP093,99C7740' DATA2,'42D0423,68Y8205' DATA3 union all
select 'ML350-G4' DATA1,'CN762100JN' DATA2,'40T1566' union all
select '9117-570,9117-570' DATA1,'6563AAD,6563ADD' DATA2,'39J2779,12R9259' union all
select 'ML350-G4' DATA1,' CN762100JN' DATA2,'40T1566' union all
select '9117-570,9117-570' DATA1,'65BC4AC,65BC4BC' DATA2,'12R9259,12R9259' union all
select 'VNX5300' DATA1,'CKM00123801942' DATA2,'5049197' union all
select '9117-570' DATA1,'655EE6C' DATA2,'39J0859' union all
select '9117-570' DATA1,'655EE6C' DATA2,'39J0859' union all
select '9117-570' DATA1,'6569EBD' DATA2,'03N6325' union all
select '7042-CR6' DATA1,'063EBDC' DATA2,'81Y9727' union all
select '7042-CR6' DATA1,'063EBDC' DATA2,'81Y9729' union all
select '3588-F4A ,3588-F4A' DATA1,'7896202 ,7896265' DATA2,'LTO Drive,LTO Drive'
)
select --* , LEN(a.DATA1) - LEN(replace(a.DATA1,',','')) v
, DBO.MssqlINSTR(A.DATA1,',',B.NUM)
, DBO.MssqlINSTR(A.DATA2,',',B.NUM)
, DBO.MssqlINSTR(A.DATA3,',',B.NUM)
from res a
cross apply
(
select number + 1 num
from master.dbo.spt_values
where type = 'P'
and number <= LEN(a.DATA1) - LEN(replace(a.DATA1,',',''))
) b
-
jude
2017.01.03 19:12
sql server 2005 로 작성해봤습니다.6개 짜리 데이터도 만들어봤습니다.with resas(select '8853-G4K,8852-4YK,1,A,ZZZZ,YELLOW' DATA1,'99GP093,99C7740,22,BB,YYY,BLACK' DATA2,'42D0423,68Y8205,333,CCC,XX,WHITE' DATA3 ,1 SEQ union allselect 'ML350-G4' DATA1,'CN762100JN' DATA2,'40T1566' ,2 SEQ union allselect '9117-570,9117-570' DATA1,'6563AAD,6563ADD' DATA2,'39J2779,12R9259' ,3 SEQ union allselect 'ML350-G4' DATA1,'CN762100JN' DATA2,'40T1566' ,4 SEQ union allselect '9117-570,9117-570' DATA1,'65BC4AC,65BC4BC' DATA2,'12R9259,12R9259' ,5 SEQ union allselect 'VNX5300' DATA1,'CKM00123801942' DATA2,'5049197' ,6 SEQ union allselect '9117-570' DATA1,'655EE6C' DATA2,'39J0859' ,7 SEQ union allselect '9117-570' DATA1,'655EE6C' DATA2,'39J0859' ,8 SEQ union allselect '9117-570' DATA1,'6569EBD' DATA2,'03N6325' ,9 SEQ union allselect '7042-CR6' DATA1,'063EBDC' DATA2,'81Y9727' ,10 SEQ union allselect '7042-CR6' DATA1,'063EBDC' DATA2,'81Y9729' ,11 SEQ union allselect '3588-F4A ,3588-F4A' DATA1,'7896202 ,7896265' DATA2,'LTO Drive,LTO Drive' ,12 SEQ),FIRST_DATA AS(SELECT SEQ,ROW_NUMBER() OVER(PARTITION BY SEQ ORDER BY DATA1) RN,DATA1,DATA2,DATA3,LEN(DATA1) - LEN(REPLACE(DATA1,',','')) ROWCNT,CHARINDEX(',',DATA1) FIRST_COMMA_POS1,CHARINDEX(',',DATA2) FIRST_COMMA_POS2,CHARINDEX(',',DATA3) FIRST_COMMA_POS3FROM RES A,MASTER.DBO.SPT_VALUES BWHERE LEN(DATA1) - LEN(REPLACE(DATA1,',','')) >= B.NUMBERAND B.TYPE ='P'),CTE_DATA AS(SELECT SEQ,RN,DATA1,DATA2,DATA3,CASE FIRST_COMMA_POS1 WHEN 0 THEN LEN(DATA1) ELSE FIRST_COMMA_POS1 END END_POSITION1,CASE FIRST_COMMA_POS2 WHEN 0 THEN LEN(DATA2) ELSE FIRST_COMMA_POS2 END END_POSITION2,CASE FIRST_COMMA_POS3 WHEN 0 THEN LEN(DATA3) ELSE FIRST_COMMA_POS3 END END_POSITION3FROM FIRST_DATAWHERE RN = 1UNION ALLSELECT A.SEQ,B.RN,SUBSTRING(A.DATA1,END_POSITION1 + 1,LEN(A.DATA1)) DATA1,SUBSTRING(A.DATA2,END_POSITION2 + 1,LEN(A.DATA2)) DATA2,SUBSTRING(A.DATA3,END_POSITION3 + 1,LEN(A.DATA3)) DATA3,CHARINDEX(',',SUBSTRING(A.DATA1,END_POSITION1 + 1,LEN(A.DATA1))) END_POSITION1,CHARINDEX(',',SUBSTRING(A.DATA2,END_POSITION2 + 1,LEN(A.DATA2))) END_POSITION2,CHARINDEX(',',SUBSTRING(A.DATA3,END_POSITION3 + 1,LEN(A.DATA3))) END_POSITION3FROM CTE_DATA A,FIRST_DATA BWHERE A.SEQ = B.SEQAND A.RN +1 = B.RN)SELECT REPLACE(SUBSTRING(DATA1,1,CASE END_POSITION1 WHEN 0 THEN LEN(DATA1) ELSE END_POSITION1 END),',','') DATA1,REPLACE(SUBSTRING(DATA2,1,CASE END_POSITION2 WHEN 0 THEN LEN(DATA2) ELSE END_POSITION2 END),',','') DATA2,REPLACE(SUBSTRING(DATA3,1,CASE END_POSITION3 WHEN 0 THEN LEN(DATA3) ELSE END_POSITION3 END),',','') DATA3FROM CTE_DATAORDER BY SEQ,RN;
http://www.sqler.com/893127 와 비슷한 질문이네요.
함수를 만들어서 이용하시면 편할듯 합니다.