카테고리 없음

심심해서 쿼리하나 짜보았다.

보미아빠 2010. 12. 15. 15:11


DECLARE @TBL TABLE
(
 M01  VARCHAR(50)
,M02  VARCHAR(50)
,M03  VARCHAR(50)
 )
INSERT @TBL
SELECT '홍길동','',''
UNION
SELECT '이순신','',''
UNION
SELECT '','이순욱',''
UNION
SELECT '','장길자',''
UNION
SELECT '','오마니',''
UNION
SELECT '','마징가',''
UNION
SELECT '','','싱싱이'


SELECT MAX(M01) M01, MAX(M02) M02, MAX(M03) M03
  FROM (SELECT
      CASE WHEN CASE WHEN M01 = '' THEN 0 ELSE 1 END = 1 THEN ROW_NUMBER() OVER (PARTITION BY CASE WHEN M01 = '' THEN 0 ELSE 1 END ORDER BY (SELECT 1)) ELSE 0 END
    + CASE WHEN CASE WHEN M02 = '' THEN 0 ELSE 1 END = 1 THEN ROW_NUMBER() OVER (PARTITION BY CASE WHEN M02 = '' THEN 0 ELSE 1 END ORDER BY (SELECT 1)) ELSE 0 END
    + CASE WHEN CASE WHEN M03 = '' THEN 0 ELSE 1 END = 1 THEN ROW_NUMBER() OVER (PARTITION BY CASE WHEN M03 = '' THEN 0 ELSE 1 END ORDER BY (SELECT 1)) ELSE 0 END G
    , *
    FROM @TBL) A
 GROUP BY G