데이터베이스를 사랑하는 사람들의 모임 데이터베이스 사랑넷

MS-SQL Q&A 6840 게시물 읽기

No. 6840

쿼리 질문입니다.
작성자	둥둥이	작성일	2014-08-08 16:15ⓒ 2014-08-08 16:19ⓜ	조회수	6,745

a 테이블에 들어있는 데이타 들입니다.

일자	지점	팀
2014-07-02	a	1팀
2014-07-02	b	2팀
2014-07-02	c	1팀
2014-07-01	d	2팀
2014-07-01	e	1팀
2014-07-01	f	2팀

결과를 아래와 같이 나오게 할려고 합니다.

일자	1팀	2팀
2014-07-02	a	b
2014-07-02	c
2014-07-01	e	d
2014-07-01		f

고수님들의 조언을 부탁드립니다.

이 글에 대한 댓글이 총 10건 있습니다.

WITH tbl ( dd, bb, tt )

(

SELECT '2014-07-02', 'a', '1팀'

UNION ALL SELECT '2014-07-02', 'b', '2팀'

UNION ALL SELECT '2014-07-02', 'c', '1팀'

UNION ALL SELECT '2014-07-01', 'd', '2팀'

UNION ALL SELECT '2014-07-01', 'e', '1팀'

UNION ALL SELECT '2014-07-01', 'f', '2팀'

)

, t2 ( dd, bb, tt, rn )

(

SELECT dd, bb, tt, ROW_NUMBER() OVER ( PARTITION BY tt, dd ORDER BY dd DESC ) AS rn

FROM tbl

)

SELECT ISNULL( t1.dd, t2.dd ) AS dd

, ISNULL( t1.bb, '' ) AS [1팀], ISNULL( t2.bb, '' ) AS [2팀]

FROM

(

SELECT *

FROM t2

WHERE tt = '1팀'

) AS t1

FULL OUTER JOIN

(

SELECT *

FROM t2

WHERE tt = '2팀'

) AS t2

ON t1.dd = t2.dd AND t1.rn = t2.rn

ORDER BY dd desc

;

우욱님이 2014-08-10 11:08에 작성한 댓글입니다. Edit Ｘ

우욱님 답변감사합니다.

일자	지점	팀
2014-07-02	a	1팀
2014-07-02	b	2팀
2014-07-02	c	1팀
2014-07-02	d	3팀
2014-07-01	e	2팀
2014-07-01	f	1팀
2014-07-01	g	2팀
2014-07-01	h	3팀
2014-07-01	i	3팀

a 테이블에 위와 같이 3팀이 들어있을때

ISNULL( t1.dd, t2.dd ) AS dd 이부분을 어떻게 처리해야 할까요?

둥둥이님이 2014-08-11 13:36에 작성한 댓글입니다. Edit Ｘ

COALESCE( t1.dd, t2.dd, t3.dd )

우욱님이 2014-08-11 14:40에 작성한 댓글입니다. Edit Ｘ

SELECT dd
     , MIN(CASE tt WHEN '1팀' THEN bb END) "1팀"
     , MIN(CASE tt WHEN '2팀' THEN bb END) "2팀"
     , MIN(CASE tt WHEN '3팀' THEN bb END) "3팀"
FROM (SELECT dd, bb, tt
             , ROW_NUMBER() OVER(PARTITION BY dd, tt ORDER BY bb) rn
          FROM tbl
        ) a
GROUP BY dd, rn
ORDER BY dd DESC, rn
;

마농(manon94)님이 2014-08-11 15:13에 작성한 댓글입니다.

우욱님 답변 감사합니다.

알려주신대로

delete from t2

insert into t2 (dd, bb, tt, rn )
select dd, bb, tt
     , ROW_NUMBER() OVER ( PARTITION BY tt, dd ORDER BY dd DESC ) AS rn
from tbl

SELECT coalesce( t1.dd, t2.dd, t3.dd, t4.dd, t5.dd, t6.dd ) AS dd
     , ISNULL( t1.bb, '' ) AS [1팀]
     , ISNULL( t2.bb, '' ) AS [2팀]
     , ISNULL( t3.bb, '' ) AS [3팀]
     , ISNULL( t4.bb, '' ) AS [4팀]
     , ISNULL( t5.bb, '' ) AS [5팀]
     , ISNULL( t6.bb, '' ) AS [6팀]
FROM (SELECT *
          FROM t2
         WHERE tt = '1팀'
       ) AS t1
FULL OUTER JOIN ( SELECT *
                      FROM t2
                     WHERE tt = '2팀'
                  ) AS t2 ON t1.dd = t2.dd AND t1.rn = t2.rn
FULL OUTER JOIN ( SELECT *
                      FROM t2
                     WHERE tt = '3팀'
                  ) AS t3 ON t1.dd = t3.dd AND t1.rn = t3.rn
FULL OUTER JOIN ( SELECT *
                      FROM t2
                     WHERE tt = '4팀'
                  ) AS t4 ON t1.dd = t4.dd AND t1.rn = t4.rn
FULL OUTER JOIN ( SELECT *
                      FROM t2
                     WHERE tt = '5팀'
                  ) AS t5 ON t1.dd = t5.dd AND t1.rn = t5.rn
FULL OUTER JOIN ( SELECT *
                      FROM t2
                     WHERE tt = '6팀'
                  ) AS t6 ON t1.dd = t6.dd AND t1.rn = t6.rn
ORDER BY dd desc

위의 알려주신대로 쿼리를 작성해서 데이타를 가져왔습니다.

(실제쿼리도 위와같은 방식으로 되어 있습니다.)

그러나 실제로 쿼리를 날려서 데이타를 가져오니

아래와 같은 현상이 발생이 됩니다.

(다 이렇게 나타나는건 아니고 일부 데이타가

아래와 같이 나타납니다.

실제로는 더 많은 팀이 존재합니다.)

일시	1팀	2팀	3팀	4팀	5팀	6팀
2014-07-20			k
2014-07-20				z
2014-07-20					q

조언 부탁드립니다.

둥둥이님이 2014-08-11 16:22에 작성한 댓글입니다.
이 댓글은 2014-08-11 16:31에 마지막으로 수정되었습니다. Edit Ｘ

-- 팀이 많으면 pivot을 써서.. ㅋ

CREATE TABLE #tbl (

dd DATE NOT NULL

, bb VARCHAR(50) NOT NULL -- branch

, tt NVARCHAR(20) NOT NULL -- team

)

;

INSERT INTO #tbl ( dd, bb, tt )

SELECT '2014-07-02', 'a', '1팀'

UNION ALL SELECT '2014-07-02', 'b', '2팀'

UNION ALL SELECT '2014-07-02', 'c', '1팀'

UNION ALL SELECT '2014-07-01', 'd', '2팀'

UNION ALL SELECT '2014-07-01', 'e', '1팀'

UNION ALL SELECT '2014-07-01', 'f', '2팀'

;

-- 팀 이름 생성하기

DECLARE @tnsSel NVARCHAR(MAX);

DECLARE @tnsPvt NVARCHAR(MAX);

SELECT @tnsSel = N'', @tnsPvt = N'';

SELECT @tnsSel = @tnsSel + N', ISNULL( [' + tt + N'], '''' ) AS [' + tt + ']'

, @tnsPvt = @tnsPvt + ', [' + tt + ']'

FROm (

SELECT DISTINCT tt

FROM #tbl

) AS tn

;

DECLARE @sqlTxt NVARCHAR(MAX);

SET @sqlTxt = N'SELECT dd' + @tnsSel + '

FROM (

SELECT dd, bb, tt, ROW_NUMBER() OVER(PARTITION BY dd, tt ORDER BY bb) rn

FROM #tbl

) AS wrn

PIVOT( MAX( bb ) FOR tt IN ( ' + RIGHT( @tnsPvt, LEN( @tnsPvt ) - 2 ) + ' ) ) AS pvt

ORDER BY dd DESC, rn'

;

EXEC( @sqlTxt )

;

우욱님이 2014-08-11 17:11에 작성한 댓글입니다. Edit Ｘ

우욱님

pivot를 사용해보지 않아서 우욱님께서 알려줘도 해결을 못하고 있습니다. ㅠㅠ

조금더 쉬운 방법이 없을까요?

둥둥이님이 2014-08-11 18:10에 작성한 댓글입니다.
이 댓글은 2014-08-11 18:11에 마지막으로 수정되었습니다. Edit Ｘ

-- 앞서 올려드린 놈은 여러 팀이 있어도 해당 팀 이름을

-- 모두 다 @tnsSel 과 @tnsPvt 에 넣도록 해서 조금 복잡해 보이긴 하지만

-- 결국 아래와 같은 쿼리를 생성합니다.

CREATE TABLE #tbl (

dd DATE NOT NULL

, bb VARCHAR(50) NOT NULL -- branch

, tt NVARCHAR(20) NOT NULL -- team

)

;

INSERT INTO #tbl ( dd, bb, tt )

SELECT '2014-07-02', 'a', '1팀'

UNION ALL SELECT '2014-07-02', 'b', '2팀'

UNION ALL SELECT '2014-07-02', 'c', '1팀'

UNION ALL SELECT '2014-07-01', 'd', '2팀'

UNION ALL SELECT '2014-07-01', 'e', '1팀'

UNION ALL SELECT '2014-07-01', 'f', '2팀'

;

SELECT dd, ISNULL( [1팀], '' ) AS [1팀], ISNULL( [2팀], '' ) AS [2팀]

FROM (

SELECT dd, bb, tt, ROW_NUMBER() OVER(PARTITION BY dd, tt ORDER BY bb) rn

FROM #tbl

) AS wrn

PIVOT( MAX( bb ) FOR tt IN ( [1팀], [2팀] ) ) AS pvt

ORDER BY dd DESC, rn

;

-- 위의 주황색 부분이 아까 올려드렸던 @tnsSel로 생성한 부분이고,
-- 위의 파랑색 부분이 아까 올려드렸던 @tnsPvt로 생성한 부분입니다.
-- 팀이 몇개 팀이나 될지 모르기 때문에(나중에 팀이 생성 등으로 인해 발생할 수 있는)
-- 이러한 팀 이름을 자동으로 생성하기 위한 버전이 위에 올려드린 버전입니다.
-- 지금 올려드린 놈을 보고 차분히 따라하시면 하실 수 있지 않을까 싶습니다.

우욱님이 2014-08-11 18:34에 작성한 댓글입니다. Edit Ｘ

pivot 알아두시면 이런 유사한 경우에 유용하니 이번 기회에 꼭 배우고 만다는 정신으로 함 도전해 보시는 것을 추천드립니다.

우욱님이 2014-08-11 18:36에 작성한 댓글입니다. Edit Ｘ

우욱님

친절한 설명 덕분에 잘 해결했습니다.

너무 감사드립니다. ^_^

둥둥이님이 2014-08-11 19:24에 작성한 댓글입니다. Edit Ｘ

[Top]

No.	제목	작성자	작성일	조회
6843	성능모니터	아이들	2014-08-26	5765
6842	select 후 값이 있으면 update 없으면 insert 에 대한 문의드립니다.	다키짱	2014-08-22	7222
6841	안녕하세요 쿼리에 대해 질문 드립니다. [2]	투루로맨스	2014-08-14	6553
6840	쿼리 질문입니다. [10]	둥둥이	2014-08-08	6745
6839	시퀀스 증가 질문이요 [1]	으아~~	2014-08-08	6099
6838	fn_varbintohexstr로 암호화하기 [6]	선이	2014-08-01	9245
6837	mdb 파일 연결 관련하여 질문드립니다. [1]	쿠쿠쿠	2014-08-01	8044