database.sarang.net
UserID
Passwd
Database
DBMS
ㆍMySQL
PostgreSQL
Firebird
Oracle
Informix
Sybase
MS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
MySQL Tutorials 23619 게시물 읽기
 News | Q&A | Columns | Tutorials | Devel | Files | Links
No. 23619
결과를 가로로 출력하여 보여주기(Pivot)
작성자
이경환(babocom)
작성일
2005-04-22 23:22ⓒ
2005-05-02 14:57ⓜ
조회수
38,110

세로로된 출력을 가로로 pivot 시켜서 보여주는 방법은 아래 방법 말고도

일련번호를 mod() 연산하고 그룹핑 하여 적당히 변형시키는 방법을 많이 씁니다.

(오라클과 같이 rownum 을 지원하는 경우 mod() 연산을 사용하여 많이 쓰죠~)

아래 설명을 참고하시고 경우에 따라 적절히(?) 변형하셔서 사용할 수 있습니다.

-------------------------------------------------------------

크로스탭 보고서

열이 가로로, 행이 세로로 나타나도록 결과를 회전시켜야 할 때가 있습니다. 이러한 작업을
PivotTable®이나 크로스탭 보고서 만들기 또는 데이터의 회전이라고 합니다.

분기마다 행을 하나씩 갖는 테이블 Pivot이 있다고 가정합니다. Pivot의 SELECT는 각 분기를
세로로 보여줍니다.

Year Quarter Amount
---- ------- ------
1990 1 1.1
1990 2 1.2
1990 3 1.3
1990 4 1.4
1991 1 2.1
1991 2 2.2
1991 3 2.3
1991 4 2.4

다음과 같이 연도별 행과 분기별 열을 갖는 테이블로 구성된 보고서가 만들어집니다.

Year Q1 Q2 Q3 Q4
1990 1.1 1.2 1.3 1.4
1991 2.1 2.2 2.3 2.4

다음은 Pivot 테이블을 만들어 첫 번째 테이블의 데이터로 채우는 문입니다.

CREATE TABLE Pivot
( Year SMALLINT,
Quarter TINYINT,
Amount DECIMAL(2,1) )
GO
INSERT INTO Pivot VALUES (1990, 1, 1.1)
INSERT INTO Pivot VALUES (1990, 2, 1.2)
INSERT INTO Pivot VALUES (1990, 3, 1.3)
INSERT INTO Pivot VALUES (1990, 4, 1.4)
INSERT INTO Pivot VALUES (1991, 1, 2.1)
INSERT INTO Pivot VALUES (1991, 2, 2.2)
INSERT INTO Pivot VALUES (1991, 3, 2.3)
INSERT INTO Pivot VALUES (1991, 4, 2.4)
GO

다음은 회전된 결과를 만드는 데 사용하는 SELECT 문입니다.

SELECT Year,
SUM(CASE Quarter WHEN 1 THEN Amount ELSE 0 END) AS Q1,
SUM(CASE Quarter WHEN 2 THEN Amount ELSE 0 END) AS Q2,
SUM(CASE Quarter WHEN 3 THEN Amount ELSE 0 END) AS Q3,
SUM(CASE Quarter WHEN 4 THEN Amount ELSE 0 END) AS Q4
FROM Pivot
GROUP BY Year
GO

이 SELECT 문은 분기마다 여러 행을 갖는 테이블도 처리합니다. GROUP BY는 주어진 연도의
Pivot의 모든 행을 결합하여 하나의 행으로 출력합니다. 그룹화 연산이 수행될 때 SUM 집계 속
의 CASE함수는 결과 집합의 정확한 열에 각 분기의 Amount 값이 더해지고 다른 분기의 결과
집합 열에는 0이 더해지는 방식으로 응용됩니다.

이 SELECT 문의 결과가 스프레드시트의 입력으로 사용된다면 스프레드시트에서 각 연도의 합
계가 쉽게 계산됩니다. 응용 프로그램에서 SELECT 문을 사용하면 연도별 합계를 계산하는
SELECT 문의 성능을 쉽게 향상시킬 수 있습니다. 다음 예를 참조하십시오.

SELECT P1.*, (P1.Q1 + P1.Q2 + P1.Q3 + P1.Q4) AS YearTotal
FROM (SELECT Year,
SUM(CASE P.Quarter WHEN 1 THEN P.Amount ELSE 0 END) AS Q1,
SUM(CASE P.Quarter WHEN 2 THEN P.Amount ELSE 0 END) AS Q2,
SUM(CASE P.Quarter WHEN 3 THEN P.Amount ELSE 0 END) AS Q3,
SUM(CASE P.Quarter WHEN 4 THEN P.Amount ELSE 0 END) AS Q4
FROM Pivot AS P
GROUP BY P.Year) AS P1
GO

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

sum,decode 정도와 sum, if 그리고 sum,case 모두 가능한데

MySQL도 decode가 있나요? 찾아봐야겠^^;

하나의 조건으로 뽑을때는 case문보다는 if문이 더 깔끔하군요.

그리고 단점?이 상수값들을 어떤식으로든지 먼저 알아내서

적당한 쿼리를 만들어야한다는;; 가끔은 이게 귀찮더군요.

어쩔수없는 문제지만;

 

통계쿼리에서 상당히 편합니다.

일단 단일쿼리로 원하는 결과셋을 뽑을수있어 통계쪽 작업시에

상당히 많이 사용되어지는 모습의 쿼리더군요.

김동균님이 2005-05-05 00:46에 작성한 댓글입니다. Edit

Pivoting의 질문은 mssql에서도 빈번하게 일어나는 건데요.

MSsql에서도 위와 흡사한 방식으로 구현합니다.

vesion이 2000까지는 그러나 2005로 업그레이드 하면

pivot/unpivot이라는 함수가 들어옵니다.

 

내부의 수행스크립트를 보니 역시 위의 구조대로 실행되고 있는 것을

확인했지만요 ^^

여리님이 2005-05-27 09:16에 작성한 댓글입니다. Edit
[Top]
No.
제목
작성자
작성일
조회
25898[옮김] MySQL DataBase 서버 튜닝 - MySQL Administrator
문태준
2006-09-30
62714
25897[옮김] MySQL DataBase 서버 튜닝 - Connection과 Memory
문태준
2006-09-30
21481
24113[Tip]과연 쿼리의 끝은 어디인가?? [7]
이경환
2005-07-23
38067
23619결과를 가로로 출력하여 보여주기(Pivot) [2]
이경환
2005-04-22
38110
23534SAP DB용 Python Client
남녀평등
2002-12-04
28496
23533SAPDB + JSP(PHP)를 이용한 간단한 페이징 팁
신정호
2002-12-04
19087
23532SAPDB PYTHON에서 SAPDBAPI를 이용한 접속
남녀평등
2002-12-04
14055
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2024 DSN, All rights reserved.
작업시간: 0.030초, 이곳 서비스는
	PostgreSQL v16.4로 자료를 관리합니다