A Table 에 있는 divString Column의 CSV 구분자(,)을 이용해서 B Table 에 넣으시오.
A Table
| id | divString |
| 1 | 121,11,34,24 |
| 2 | 11,3,456,23 |
B Table
| id | divString |
| 1 | 121 |
| 1 | 11 |
| 1 | 34 |
| 1 | 24 |
| 2 | 11 |
| 2 | 3 |
| 2 | 456 |
| 2 | 23 |
SELECT aTB.id, substring(aTB.divString, bTB.number, bTB.endP-bTB.number) as rtnString
FROM aTable aTB CROSS APPLY(
SELECT top(10)
num as number,
CHARINDEX(',',aTB.divString+',',num) as endP
from numTable
where num = charindex(',',','+aTB.divString,num)
ORDER BY num
) bTB
ORDER BY 1
풀이과정]
Create Table aTable(
id INT IDENTITY(1,1) NOT NULL,
divString Char(2)
)
Alter Table aTable Alter Column divString Char(20)
Select * from aTable
INSERT INTO aTable (divString) Values('121,11,34,24');
INSERT INTO aTable (divString) Values('11,3,456,23');
Create Table bTable(
id INT NOT NULL,
divString Char(20)
)
Select * from bTable
// csv(,) 구분자 골라내기
select charindex(',',divString) from aTable;
select charindex(',',divString,4) from aTable;
select patindex('%,%',divString) from aTable;
// 문자 잘라내기
select divString,
substring(divString,1,charindex(',',divString)-1) ,
substring(divString,charindex(',',divString)+1,len(divString))
from aTable;
Create table numTable (num int identity(1,1) not null)
while 1=1
begin
insert into numTable default values;
if @@identity = 900
break
end
insert into bTable (id,divString)(
SELECT aTB.id, substring(aTB.divString, bTB.number, bTB.endP-bTB.number) as rtnString
FROM aTable aTB CROSS APPLY(
SELECT top(10)
num as number,
CHARINDEX(',',aTB.divString+',',num) as endP
from numTable
where num = charindex(',',','+aTB.divString,num)
ORDER BY num
) bTB)
ORDER BY 1
select * from bTable
[또다른 A 풀이1]
DECLARE @id int, @no varchar(100)
DECLARE @i int, @cnt int
// 커서 생성
DECLARE jeffCur CURSOR FOR
SELECT id,no FROM aTable
OPEN jeffCur
FETCH NEXT FROM jeffCur INTO @id, @no
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @cnt = len(@no)-len(replace(@no,',',''))
SET @i=0
WHILE @cnt > @i
BEGIN
INSERT INTO bTable (id,no) VALUES (@id,SUBSTRING( @no,1,CHARINDEX(',',@no)-1))
SET @no=SUBSTRING( @no,CHARINDEX(',',@no)+1,LEN(@no))
SET @i=@i+1
END
INSERT INTO bTable (id,no) VALUES (@id,@no)
FETCH NEXT FROM jeffCur INTO @id, @no
END
CLOSE jeffCur
DEALLOCATE jeffCur
[또다른 B 풀이1]
ALTER PROCEDURE [dbo].[CSVTOINTO]
AS
-- aTable 문제 테이블
-- bTable 결과테이블
declare @rowcount TINYINT
declare @rcount TINYINT
declare @id TINYINT
declare @no varchar(20)
declare @separator_position int
declare @array_value varchar(20)
set @rcount = 1
SELECT @rowcount = count(*) FROM aTable
while @rcount < (@rowcount + 1)
begin
set @id = @rcount
select @no = no from aTable where id = @id
set @no = @no + ','
while patindex('%,%' , @no) <> 0
begin
select @separator_position = patindex('%,%' , @no)
select @array_value = left(@no, @separator_position - 1)
Insert bTable (id, no) Values (@id, @array_value)
-- select @id, @array_value
select @no = stuff(@no, 1, @separator_position, '')
end
set @rcount = @rcount + 1
end
SELECT * FROM bTable