이전 글인 테이블을 , 로 구분된 로우로 바꾸기 에서 coalesce 와 for xml path('') 을 이용하여 1개의 row 혹은 다수의 row를 , 로 구분된 단일 컬럼값으로 나오게 하는 글을 작성하였습니다.
예제로만 coalesce를 써서 coalesce에 대해 좀더 자세한 설명을 적어보려고 합니다.
일단 msnd의 coalesce 를 보시죠.
인수 중에서 Null이 아닌 첫 번째 식을 반환합니다.
구문
COALESCE ( expression [ ,...n ] ) |
인수
- expression
-
임의 데이터 형식의 식입니다.
- n
-
여러 개의 식을 지정할 수 있음을 나타내는 자리 표시자입니다. 모든 식의 형식이 동일하거나 동일한 형식으로 암시적으로 변환할 수 있어야 합니다.
반환 값
expression에 있는 형식 집합 중에서 우선 순위가 가장 높은 형식을 반환합니다.
select coalesce(값1, 값2, 값3, ... , 값n)
이렇게 있다고 했을 때 처음으로 null이 아닌 값을 반환하는 건데요, 값1이 null 이라면 값2, 값2도 null 이라면 값3 이런식으로 값을 반환하게 됩니다.
declare @val1 varchar(10)
declare @val2 varchar(10)
declare @val3 varchar(10)
declare @val2 varchar(10)
declare @val3 varchar(10)
set @val2 = '꽃미남'
select coalesce(@val1, @val2, @val3)
결과
=====================================================
val
----------
꽃미남
결과
=====================================================
val
----------
꽃미남
(1개 행 적용됨)
여기까지가 일반적인 용도이며, 전 글에서 작성한대로 다수의 값을 하나로 묶을때도 사용할 수 있습니다.
일종의 꽁수죠.
-- 예제에서 사용할 테이블을 생성합니다.
create table #t1 (val varchar(10))
-- 값 입력
insert into #t1 values('값1')
insert into #t1 values('값2')
insert into #t1 values('값3')
-- 변수 선언
declare @stringValue varchar(200)
-- 테이블 값 가져오기
select @stringValue = coalesce(@stringValue + ',' + val, val) as '@stringValue'
from #t1
결과
======================================================
@stringValue
---------------------------
꽃미남1,꽃미남2,꽃미남3
create table #t1 (val varchar(10))
-- 값 입력
insert into #t1 values('값1')
insert into #t1 values('값2')
insert into #t1 values('값3')
-- 변수 선언
declare @stringValue varchar(200)
-- 테이블 값 가져오기
select @stringValue = coalesce(@stringValue + ',' + val, val) as '@stringValue'
from #t1
결과
======================================================
@stringValue
---------------------------
꽃미남1,꽃미남2,꽃미남3
(1개 행 적용됨)
위의 예제에서 다음 select 절을 보시죠.
select @stringValue = coalesce(@stringValue + ',' + val, val) as '@stringValue'
from #t1
#t1 테이블의 첫 row('꽃미남1')에서 coalesce의 첫번째 값인 @stringValue 은 선언되고 값이 셋팅되지 않았기 때문에 null 인 상태 입니다.
여기에 테이들의 val 컬럼값을 더해도 결과는 여전히 null 입니다.(null + '어떤값' = null)
그러므로 다음 값인 val이('꽃미남1') @stringValue 에 들어가게 됩니다.
-> @stringValue : 꽃미남1
이제 그 다음 row인 '꽃미남2' 가 됐습니다.
coalesce 절에서 현재 첫번째 값인 @stringValue + ',' + val = '꽃미남1,꽃미남2' 가 됩니다.
null 이 아닌 값이므로 이 값이 @stringValue 에 담기게 됩니다.
-> @stringValue : 꽃미남1, 꽃미남2
세번째 row인 '꽃미남3' 은 따로 설명하지 않아도 척 알아차리셨을 것이라 생각됩니다.
이런 원리로 row를 column 값으로 만들 수 있는 것입니다.
그러나 한가지 조심하셔야 할 부분이 있습니다.
select coalesce( (select top 1 valuX from tableX), (select top 1 valueY from tableY))
와 같이 coalesce에 서브 쿼리가 들어갈때는 실행계획이 잘못 풀리는 경우가 있습니다.
이렇게 사용하실때에는 반드시 실행계획을 확인하시고 사용하시기 바랍니다.
이렇게 row to coulmn이나 column to row(이건 coalesce가 아닌 다른 방법을 사용합니다)는 업무에서도 생각보다 자주 쓰이는 기능이므로 익혀 놓으시면 꼭 유용하게 쓰일때가 있을 것입니다.
P.S : 위의 coalesce에 서브쿼리가 들어가는 경우는 isnull과 비교되어 성능튜닝에 사용되는 경우가 있습니다. 이것은 다음에 다시 포스팅 하겠습니다.(언제나 할까요.. ^ ^;;)
