[SQL2k8] 그룹핑해서 컬럼별로 최근 데이터 가져오기

/********************************************************************************************
-- Title : [SQL2k8] 그룹핑해서 컬럼별로 최근 데이터 가져오기
-- Key word : group by row_number 그룹핑 grouping
********************************************************************************************/

-- 아래 그림 상단에서 id별로 그룹핑하고 각 컬럼별로 seq가 작은 것의 값을 가져오도록..
-- 결과는 그림 하단처럼..

USE tempdb;
GO

DROP TABLE ttt;

-- 테이블 생성
CREATE TABLE ttt
( id NCHAR(2) NOT NULL
, seq INT NOT NULL
, c1 NCHAR(1) NULL
, c2 BIGINT NULL
, c3 BIGINT NULL
, constraint pk_ttt primary key (id, seq)
);

-- 데이터 입력
INSERT INTO ttt
SELECT 'CC', 24,    'Z',          NULL,         NULL     UNION ALL
SELECT 'CC', 17,    NULL,         NULL,   1111111111     UNION ALL
SELECT 'AA', 4,     'A',          NULL,         NULL     UNION ALL
SELECT 'AA', 7,     'B',    2222222222,         NULL     UNION ALL
SELECT 'AA', 9,     NULL,         NULL,   3333333333     UNION ALL
SELECT 'BB', 6,     'K',    5555555555,   4444444444     UNION ALL
SELECT 'BB', 9,     NULL,   6666666666,         NULL     UNION ALL
SELECT 'BB', 3,     'F',          NULL,         NULL;

-- 확인
SELECT * FROM ttt
ORDER BY id, seq;

-- CASE 1)
-- 행 제약이 없으면 불가/다양한 조작 가능
select a.id
     , isnull(max(case when a.seq2 = 1 then c1 end),
       isnull(max(case when a.seq2 = 2 then c1 end),
       isnull(max(case when a.seq2 = 3 then c1 end),
       isnull(max(case when a.seq2 = 4 then c1 end),
              max(case when a.seq2 = 5 then c1 end))))) "c1"
     , isnull(max(case when a.seq2 = 1 then c2 end),
       isnull(max(case when a.seq2 = 2 then c2 end),
       isnull(max(case when a.seq2 = 3 then c2 end),
       isnull(max(case when a.seq2 = 4 then c2 end),
              max(case when a.seq2 = 5 then c2 end))))) "c2"
     , isnull(max(case when a.seq2 = 1 then c3 end),
       isnull(max(case when a.seq2 = 2 then c3 end),
       isnull(max(case when a.seq2 = 3 then c3 end),
       isnull(max(case when a.seq2 = 4 then c3 end),
              max(case when a.seq2 = 5 then c3 end))))) "c3"
from
(
    select *
         , ROW_NUMBER( ) OVER
           (PARTITION BY id
            ORDER BY seq ASC
           ) "seq2"
    from ttt
) a
group by a.id;

-- CASE 2)
-- 재 호출 다량/인덱스 잘 하면 성능 좋아질 수도..case-by
SELECT A.id
     , (SELECT TOP 1 c1 FROM ttt WHERE id = A.id AND c1 IS NOT NULL ORDER BY seq) "c1"
     , (SELECT TOP 1 c2 FROM ttt WHERE id = A.id AND c2 IS NOT NULL ORDER BY seq) "c2"
     , (SELECT TOP 1 c3 FROM ttt WHERE id = A.id AND c3 IS NOT NULL ORDER BY seq) "c3"
FROM ttt A
GROUP BY id;
GO

-- CASE 3)
-- CLOB의 경우 병합 불가(가능하단다 얘야~), 그래도 간편.
SELECT x.id
     , SUBSTRING(x.c1, CHARINDEX('|', x.c1) + 1, LEN(x.c1)) "c1"
     , CAST(SUBSTRING(x.c2, CHARINDEX('|', x.c2) + 1, LEN(x.c2)) AS BIGINT) "c2"
     , CAST(SUBSTRING(x.c3, CHARINDEX('|', x.c3) + 1, LEN(x.c3)) AS BIGINT) "c3"
FROM
(
    SELECT id
          , MIN(CAST(seq AS VARCHAR(20))+ '|' + c1) "c1"
          , MIN(CAST(seq AS VARCHAR(20))+ '|' + CAST(c2 AS NVARCHAR(20))) "c2"
          , MIN(CAST(seq AS VARCHAR(20))+ '|' + CAST(c3 AS NVARCHAR(20))) "c3"
    FROM
        ttt GROUP BY id
) x;
GO