피벗에 사용법에 대해서 알아보겠습니다. 엑셀의 피벗테이블을 사용해보신분들은 개념을 쉽게 알수 있을겁니다. 위키백과에 검색해보니 아래와 같이 알려주네요. 행,열로된 데이터를 통계표로 나타내주는 부분으로 합계,평균,기타통계등을 출력할수 있게 해줍니다.
1. PIVOT에 대해서 알려보겠습니다.
특정행의 값을 열로 나타내고 싶은경우가 있습니다. 그때 PIVOT함수를 사용하면됩니다. 먼저 MSDN의 자료를 검색해보면 구문이 아래와 같습니다.
docs.microsoft.com/ko-kr/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-ver15
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
SELECT <non-pivoted column>,
[first pivoted column] AS <column name>,
[second pivoted column] AS <column name>,
...
[last pivoted column] AS <column name>
FROM
(<SELECT query that produces the data>)
AS <alias for the source query>
PIVOT
(
<aggregation function>(<column being aggregated>)
FOR
[<column that contains the values that will become column headers>]
IN ( [first pivoted column], [second pivoted column],
... [last pivoted column])
) AS <alias for the pivot table>
<optional ORDER BY clause>;
|
|
먼저 시험결과라는 테이블을 만들어 테스트 데이터를 넣어보겠습니다.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
Create table ExamResult
(
exameid int identity(1,1) primary key ,
class varchar(10),
student varchar(20),
subject varchar(10),
score int
)
go
insert into examresult values ('1반','학생1','수학',100)
insert into examresult values ('1반','학생1','과학',80)
insert into examresult values ('1반','학생1','국어',90)
insert into examresult values ('1반','학생1','수학',80) insert into examresult values ('2반','학생2','수학',100)
insert into examresult values ('2반','학생2','과학',100)
insert into examresult values ('2반','학생2','국어',90)
insert into examresult values ('3반','학생3','수학',80)
insert into examresult values ('3반','학생3','과학',80)
insert into examresult values ('3반','학생3','국어',60)
|
|
PIVOT관련 조회하기전에 먼저 평균에대한 결과값을 그룹지어 출력해보도록 하겠습니다.
|
1
2
3
4
|
select class,subject,avg(score) '평균' from ExamResult
group by subject,class
order by subject
|
|
쿼리결과값입니다. 클래스,과목별로 평균값이 나타납니다.
이제 PIVOT함수를 사용해서 과목을 행로하고 반을 열로하여 점수합계를 알아보도록 하겠습니다. 열에 표기하고 싶은 항목을 PIVOT부분의 FOR 항목에 나타내면됩니다.
|
1
2
3
4
5
6
7
8
9
10
11
12
|
-- Pivot table with one row and five columns
-- 과목별 결과값
SELECT subject,
[1반], [2반], [3반]
FROM
(SELECT class, subject,score
FROm ExamResult) AS ExamResult
PIVOT
(
AVG(score)
FOR class IN ([1반], [2반], [3반])
) AS PivotTable
|
|
결과값
반별로 과목통계를 사용하고 싶을때에 아래과 같이 출력하면됩니다.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
-- Pivot table with one row and five columns
SELECT class,
[과학], [국어], [수학]
FROM
(SELECT class, subject,score
FROm ExamResult) AS ExamResult
PIVOT
(
AVG(score)
FOR subject IN ([과학], [국어], [수학])
) AS PivotTable;
|
|
결과값
2.UNPIVOT에 대해 알아보겠습니다.
pivot과 반대개념으로 생각하면됩니다. 열에 표기되어있는 부분을 행의 표기할때 사용하면됩니다. 아래의 경우데이터를 보면 열에서 과목별로 평균을 나타내고 있습니다. UNPIVOT문에서 score FOR subject 이부분만 유의하시면 됩니다. 과목에 대한부분을 unpivot할 예정이며 과목에 대한부분이 점수를 기록하고 있기때문에 score라고 컬럼명칭을 임의로 지정하였습니다.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
|
Create table ExamResult1
(
exameid int identity(1,1) primary key ,
class varchar(10),
과학 int,
수학 int,
국어 int
)
go
insert into ExamResult1 values ('1반',100,90,80)
insert into ExamResult1 values ('2반',100,90,80)
insert into ExamResult1 values ('3반',100,90,80)
/*데이터조회 */
select * from ExamResult1
/* UNPIVOT */
SELECT class, subject, score
FROM
(SELECT class, 과학, 수학, 국어
FROM ExamResult1) p
UNPIVOT
(score FOR subject IN
(과학, 수학, 국어)
)AS unpvt;
GO
|
|
UNPIVOT결과입니다.
출처,참고)
docs.microsoft.com/ko-kr/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-ver15
'프로그래밍 > DB' 카테고리의 다른 글
| [DB] MSSQL 데이터가져오기,내보내기(엑셀가져오기,엑셀내보내기) (0) | 2020.10.12 |
|---|---|
| [DB] MSSQL 데이터베이스 다이어그램을 통하여 ERD작성 (0) | 2020.10.11 |
| [DB] MSSQL 서비스 기본포트(1433) 변경방법 (0) | 2020.10.09 |
| [DB] MSSQL 중복데이터 확인, 중복값제거 쿼리문 (0) | 2020.10.09 |
| [DB] MSSQL 뷰테이블 생성 및 조회권한설정 (0) | 2020.10.05 |