[Database] [MSSQL] 컴마(혹은 특수 문자)로 구분된 값을 나누어서 행단위로 만들기.

* 특정 문자로 구분된 값을 나누어서 집계를 해야하는 경우가 있다. 아래의 사이트들에 좋은 예제가 있어서 가져왔다.

* 방법 1. XML 파싱
* 출처 : http://blog.sqlauthority.com/2015/04/21/sql-server-split-comma-separated-list-without-using-a-function/

-- 예제를 위한 임시 테이블 생성
DECLARE @t TABLE (
    EmployeeID INT,
    Certs VARCHAR(8000)
)
INSERT @t VALUES (1,'B.E.,MCA, MCDBA, PGDCA'), (2,'M.Com.,B.Sc.'), (3,'M.Sc.,M.Tech.')

-- 방법 1. XML 이용
SELECT EmployeeID,
LTRIM(RTRIM(m.n.value('.[1]','varchar(8000)'))) AS Certs
FROM
        (
        SELECT EmployeeID,CAST('<XMLRoot><RowData>' + REPLACE(Certs,',','</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) AS x
        FROM   @t
        )t
CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)

* 방법 2. 쿼리 파싱
* 출처 : http://www.sqlteam.com/article/parsing-csv-values-into-multiple-rows

-- 컴마로 구분된 값이 있는 임시테이블
DECLARE @Quotes table(
Author varchar(50),
Phrase varchar(500)
)
insert into @Quotes values ('Shakespeare', 'A,rose,by,any,other,name,smells,just,as,sweet')
insert into @Quotes values ('Kipling', 'Across,the,valley,of,death,rode,the,six,hundred')
insert into @Quotes values ('Coleridge', 'In,Xanadu,did,Kubla,Khan,...,,,,damn,I,forgot,the,rest,of,it')
insert into @Quotes values ('Descartes', 'I,think,therefore,I,am')
insert into @Quotes values ('Volk', 'I,think,therefore,I,need,another,beer')
insert into @Quotes values ('Feldman', 'No,it,is,pronounced,I,gor')
insert into @Quotes values ('Simpson', 'Mmmmmm,donuts')
insert into @Quotes values ('Fudd', 'Be,vewwy,vewwy,quiet,I,am,hunting,wabbits')

-- 결과를 저장할 임시테이블
DECLARE @OnlyWords table(
Author varchar(50),
Word varchar(50)
)

-- 연산에 필요한 임시테이블
DECLARE @Tally table(
ID int
)
DECLARE @idx int
SET @idx = 1
WHILE (@idx <= 8000)
BEGIN
    insert into @Tally (ID) values (@idx)
    SELECT @idx = @idx + 1
END

-- 방법 2. SELECT 쿼리
SELECT Author, NullIf(SubString(',' + Phrase + ',' , ID , CharIndex(',' , ',' + Phrase + ',' , ID) - ID) , '') AS Word
FROM @Tally, @Quotes
WHERE ID <= Len(',' + Phrase + ',') AND SubString(',' + Phrase + ',' , ID - 1, 1) = ','
AND CharIndex(',' , ',' + Phrase + ',' , ID) - ID > 0 -- Null값을 표시하려면 이 열을 주석처리

-- 결과테이블에 저장하는 쿼리
INSERT INTO @OnlyWords
SELECT Author, NullIf(SubString(',' + Phrase + ',' , ID , CharIndex(',' , ',' + Phrase + ',' , ID) - ID) , '') AS Word
FROM @Tally, @Quotes
WHERE ID <= Len(',' + Phrase + ',') AND SubString(',' + Phrase + ',' , ID - 1, 1) = ','
AND CharIndex(',' , ',' + Phrase + ',' , ID) - ID > 0 -- Null값을 표시하려면 이 열을 주석처리

-- 결과 확인
select * from @OnlyWords