첫 번째는 정적쿼리(Static Query)를 이용해 사용자를 기준으로 특정년도에 구입한 금액을 월별로 가져오는 예제이고,
두 번째는 동적쿼리(Dynamic Query)를 이용해 특정년도의 월을 기준으로 사용자가 구입한 금액을 가져오는 예제이다.
첫번째 예제의 경우는 1 년이 12 개월이라고 정해져 있기 때문에 개발자가 쿼리 작성시 1, 2, 3 .. 10, 11, 12 월까지 직접 명시해줄 수 있기 때문에 정적 쿼리를 사용하고,
두번째 예제의 경우는 회원의 수가 증가 또는 감소하고 그에 따라 사용자의 이름도 동적으로 발생되기 때문에 개발자가 쿼리 작성시 첫번째 예제 처럼 사용자의 이름을 직접 명시해줄 수가 없다. 그렇기 때문에 동적 쿼리를 사용한다.
자, 이제 예제를 통해 확인해보도록 하자.
일단, 예제에 들어가기 전에 간단한 사용자 정보와 구매 금액, 그리고 구입한 날짜 정보를 가지고 있는
테이블을 생성한다.
- Schema -
| 번호 | 컬럼명(영문) | 컬럼명(한글) |
| 1 | Seq | 순번 |
| 2 | UserId | 사용자ID |
| 3 | UserName | 사용자명 |
| 4 | BuyDate | 구입날짜 |
| 5 | BuyPrice | 구입금액 |
/* 테이블 생성 스크립트 */
CREATE TABLE [dbo].[Selling](
[Seq] [int] IDENTITY(1,1) NOT NULL,
[UserId] [nvarchar](50) COLLATE Korean_Wansung_CI_AS NULL,
[UserName] [nvarchar](50) COLLATE Korean_Wansung_CI_AS NULL,
[BuyDate] [datetime] NULL,
[BuyPrice] [decimal](15, 0) NULL
) ON [PRIMARY]
테이블 생성했으면 예제에서 사용할 데이타를 입력한다.
- Data -
/* 데이타 생성 스크립트 */
INSERT INTO Selling(UserId, UserName, BuyDate, BuyPrice) VALUES('cslee75', 'cslee75', '2006-02-14', 33000)
INSERT INTO Selling(UserId, UserName, BuyDate, BuyPrice) VALUES('koreait1', 'koreait1', '2006-07-30', 15200)
INSERT INTO Selling(UserId, UserName, BuyDate, BuyPrice) VALUES('jds5108', '흰둥이', '2006-11-11', 46700)
INSERT INTO Selling(UserId, UserName, BuyDate, BuyPrice) VALUES('popolio', 'popolio', '2006-12-10', 71300)
INSERT INTO Selling(UserId, UserName, BuyDate, BuyPrice) VALUES('tolkin00', '모놀로그', '2007-01-16', 22500)
INSERT INTO Selling(UserId, UserName, BuyDate, BuyPrice) VALUES('kiki80js', '너영나영', '2007-06-24', 76000)
INSERT INTO Selling(UserId, UserName, BuyDate, BuyPrice) VALUES('9914142', '깜디', '2007-10-26', 23000)
INSERT INTO Selling(UserId, UserName, BuyDate, BuyPrice) VALUES('bluewater000', '파란물', '2007-11-17', 86000)
INSERT INTO Selling(UserId, UserName, BuyDate, BuyPrice) VALUES('onemins', '싸이코더', '2008-01-01', 32000)
INSERT INTO Selling(UserId, UserName, BuyDate, BuyPrice) VALUES('onemins', '싸이코더', '2008-01-30', 30000)
INSERT INTO Selling(UserId, UserName, BuyDate, BuyPrice) VALUES('jds5108', '흰둥이', '2008-01-30', 65000)
INSERT INTO Selling(UserId, UserName, BuyDate, BuyPrice) VALUES('9914142', '깜디', '2008-02-03', 24000)
INSERT INTO Selling(UserId, UserName, BuyDate, BuyPrice) VALUES('tolkin00', '모놀로그', '2008-02-13', 17000)
INSERT INTO Selling(UserId, UserName, BuyDate, BuyPrice) VALUES('jds5108', '흰둥이', '2008-02-12', 38000)
INSERT INTO Selling(UserId, UserName, BuyDate, BuyPrice) VALUES('younho74', '유노', '2008-02-14', 33000)
INSERT INTO Selling(UserId, UserName, BuyDate, BuyPrice) VALUES('bluewater000', '파란물', '2008-02-20', 15000)
INSERT INTO Selling(UserId, UserName, BuyDate, BuyPrice) VALUES('9914142', '깜디', '2008-03-21', 73000)
INSERT INTO Selling(UserId, UserName, BuyDate, BuyPrice) VALUES('koreait1', 'koreait1', '2008-03-30', 15000)
INSERT INTO Selling(UserId, UserName, BuyDate, BuyPrice) VALUES('popolio', 'popolio', '2008-04-10', 78000)
INSERT INTO Selling(UserId, UserName, BuyDate, BuyPrice) VALUES('bluewater000', '파란물', '2008-04-11', 86000)
INSERT INTO Selling(UserId, UserName, BuyDate, BuyPrice) VALUES('onemins', '싸이코더', '2008-04-12', 12000)
INSERT INTO Selling(UserId, UserName, BuyDate, BuyPrice) VALUES('kiki80js', '너영나영', '2008-04-20', 31000)
INSERT INTO Selling(UserId, UserName, BuyDate, BuyPrice) VALUES('simybh', '별들의기', '2008-05-13', 10000)
INSERT INTO Selling(UserId, UserName, BuyDate, BuyPrice) VALUES('ppangse', '팡세', '2008-05-14', 55000)
INSERT INTO Selling(UserId, UserName, BuyDate, BuyPrice) VALUES('cslee75', 'cslee75', '2008-05-30', 3000)
INSERT INTO Selling(UserId, UserName, BuyDate, BuyPrice) VALUES('sharpkk', '꼬냥이', '2008-06-07', 27000)
INSERT INTO Selling(UserId, UserName, BuyDate, BuyPrice) VALUES('tolkin00', '모놀로그', '2008-06-16', 26000)
INSERT INTO Selling(UserId, UserName, BuyDate, BuyPrice) VALUES('wjddyd1017', '뚤', '2008-07-01', 96000)
INSERT INTO Selling(UserId, UserName, BuyDate, BuyPrice) VALUES('kiki80js', '너영나영', '2008-07-03', 18000)
INSERT INTO Selling(UserId, UserName, BuyDate, BuyPrice) VALUES('jds5108', '흰둥이', '2008-08-11', 46000)
INSERT INTO Selling(UserId, UserName, BuyDate, BuyPrice) VALUES('younho74', '유노', '2008-08-14', 51000)
INSERT INTO Selling(UserId, UserName, BuyDate, BuyPrice) VALUES('popolio', 'popolio', '2008-09-15', 59000)
INSERT INTO Selling(UserId, UserName, BuyDate, BuyPrice) VALUES('bluewater000', '파란물', '2008-09-30', 26000)
INSERT INTO Selling(UserId, UserName, BuyDate, BuyPrice) VALUES('koreait1', 'koreait1', '2008-10-01', 58000)
INSERT INTO Selling(UserId, UserName, BuyDate, BuyPrice) VALUES('best4u', '피터', '2008-10-06', 30500)
INSERT INTO Selling(UserId, UserName, BuyDate, BuyPrice) VALUES('damansa1', 'damansa1', '2008-10-06', 55700)
INSERT INTO Selling(UserId, UserName, BuyDate, BuyPrice) VALUES('sharpkk', '꼬냥이', '2008-11-20', 45600)
INSERT INTO Selling(UserId, UserName, BuyDate, BuyPrice) VALUES('ciafbi82', '멀더요원', '2008-11-23', 48800)
INSERT INTO Selling(UserId, UserName, BuyDate, BuyPrice) VALUES('popolio', 'popolio', '2008-12-03', 41000)
INSERT INTO Selling(UserId, UserName, BuyDate, BuyPrice) VALUES('cslee75', 'cslee75', '2008-12-14', 30000)
INSERT INTO Selling(UserId, UserName, BuyDate, BuyPrice) VALUES('simybh', '별들의기', '2008-12-20', 31000)
위의 쿼리를 실행하고 SELECT 문을 이용하여 데이타가 정상적으로 입력되었는지 확인한다.
/* 데이타 입력 결과 */
- Static Pivot -
/* Query */
1 : SELECT *
2 : FROM (
3 : SELECT UserId,
4 : UserName,
5 : DATEPART(month, BuyDate) AS SellingMonth,
6 : BuyPrice
7 : FROM Selling
8 : WHERE DATEPART(year, BuyDate) = 2008
9 : ) AS Selling
10 : PIVOT(SUM(BuyPrice) FOR SellingMonth IN([1], [2], [3], [4], [5], [6], [7], [8], [9], [10],
[11], [12])) AS PivotSelling
* 설명 *
3~8 : 특정년도(2008)에 대한 데이타를 가져오기 위한 쿼리.
구입일자를 월별 데이타로 묶기 위해 DATEPART(month, BuyDate) 로 월 정보만 가져옴.
10 : PIVOT 키워드를 사용하여 3~8 에서 가져온 데이타 중 SellingMonth 컬럼을 명시한
월([1]~[12])을 기준으로 하여 SUM() 함수를 이용하여 BuyPrice 컬럼의 값의 합계를 구한다.
/* Query 결과 */
- Dynamic Pivot -
/* Query */
1 : DECLARE @sql VARCHAR(MAX)
2 : DECLARE @colsNm VARCHAR(MAX)
3 : SET @colsNm = STUFF (
4 : (
5 : SELECT ',' + QUOTENAME(UserName) AS [text()]
6 : FROM (
7 : SELECT DISTINCT UserName
8 : FROM Selling
9 : ) AS BuyUserName
10 : FOR XML PATH('')
11 : ), 1, 1, ''
12 : );
13 : SET @sql = '
14 : SELECT *
15 : FROM (
16 : SELECT UserName,
17 : DATEPART(month, BuyDate) AS Month,
18 : BuyPrice
19 : FROM Selling
20 : WHERE DATEPART(year, BuyDate) = 2008
21 : ) AS Selling
22 : PIVOT(SUM(BuyPrice) FOR UserName IN(' + @colsNm + ')) AS PivotSelling';
23 : EXEC(@sql)
* 설명 *
1 : 작성한 동적쿼리를 저장하기 위한 변수 선언
2 : 모든 사용자명를 저장 위한 변수 선언
7~8 : 중복되지 않은 모든 사용자명을 조회한다.
5 : ',' + QUOTENAME(UserName) AS [text()] 를 이용하여 7~8에서 가져온 사용자명 Data를
',[사용자명]' 형식으로 변경한다.
10 : FOR XML PATH('') 를 이용하여 ',[사용자명]' 형식의 Row 데이타를
',[사용자명],[사용자명], ... [사용자명],[사용자명]' 형식의 한 컬럼(문자열)으로 만든다.
3 ,11 : STUFF() 함수를 이용하여 ',[사용자명],[사용자명], ... [사용자명],[사용자명]' 형식의
문자열 값의 맨 앞자리인 ',' 를 제거하여,
'[사용자명],[사용자명], ... [사용자명],[사용자명]' 으로 변경 후 모든 사용자명을 저장하기
위해 선언한 변수 @colsNm 에 저장한다.
13~22 : 생성한 @colsNm 를 이용하여 동적쿼리를 완성한다.
23 : EXEC 를 이용하여 쿼리 실행.
/* Query 결과 */
이상 PIVOT 기능을 이용하여 간편하게 행(Row)에서 열(Column)로 변경하는 예제를 살펴보았다.
