MSSQL PIVOT 사용해서 행을 열로 바꾸기

 

SELECT DATEPART(WEEKDAY, REG_DATE) WEEKDAY
     , SUBSTRING(CONVERT(VARCHAR(10), REG_DATE, 108), 1, 2) HOURS
     , COUNT(1) totalMemUvCnt
     , SUM(VISITCNT) totalUvCnt
     , SUM( CASE WHEN DEVICE_TYPE = 'WEB' THEN 1 ELSE 0 END ) webMemUvCnt
  FROM TBL_LOG_UV 
 WHERE REG_DATE >= '2016-06-01'
   AND REG_DATE < DATEADD(DAY, 1, '2016-06-30')
 GROUP BY SUBSTRING(CONVERT(VARCHAR(10), REG_DATE, 108), 1, 2), DATEPART(WEEKDAY, REG_DATE)

 

 

WEEKDAY	HOURS	totalMemUvCnt	totalUvCnt	webMemUvCnt
1	00	122	351	45
1	01	101	199	43
1	02	72	115	21
1	03	41	62	12
1	04	33	57	12
1	05	22	51	5
1	06	28	41	7
1	07	41	84	13
1	08	59	154	19
1	09	68	188	20
1	10	113	288	40
1	11	95	407	33
1	12	108	322	40
1	13	113	261	40
1	14	88	342	32
1	15	132	290	57
1	16	176	380	56
1	17	121	404	41
1	18	137	279	49
1	19	109	194	38
1	20	119	240	43
1	21	118	198	43
1	22	162	269	53
1	23	146	216	35
2	00	156	356	43
2	01	106	221	46
2	02	71	155	27
2	03	60	119	25
2	04	30	68	14
2	05	25	43	10
2	06	42	84	16
2	07	57	96	9
2	08	80	1169	24
2	09	125	821	56
2	10	175	1650	99
2	11	182	932	97
2	12	146	533	67
2	13	172	925	99
2	14	163	539	84
2	15	175	464	74
2	16	186	489	90
2	17	158	379	74
2	18	150	378	75
2	19	131	455	48
2	20	133	282	55
2	21	162	290	62
2	22	152	278	56
2	23	144	287	49

... 이런식으로 WEEKDAY는 1부터 7까지 (월~일)

WEEKDAY 한개마다 HOURS는  24개씩 반복(00시~23시)

 

 

여기서 WEEKDAY 1~7를 말머리 행으로 올리고 싶었다.

예를 들면 이렇게..

	totalMemUvCnt_일
HOURS	totalMemUvCnt_일	totalMemUvCnt_월	totalMemUvCnt_화	totalMemUvCnt_수	totalMemUvCnt_목	totalMemUvCnt_금	totalMemUvCnt_토
00	122	156	146	209	233	203	144
01	101	106	105	174	181	148	128
02	72	71	57	117	131	89	71
03	41	60	56	75	85	58	77
04	33	30	24	66	70	48	33
05	22	25	28	62	48	33	43
06	28	42	35	80	67	40	42
07	41	57	55	104	96	72	48
08	59	80	63	147	111	83	72
09	68	125	117	178	159	130	95
10	113	175	176	241	227	295	132
11	95	182	211	264	228	267	149
12	108	146	176	256	215	179	109
13	113	172	189	211	248	356	99
14	88	163	215	236	253	307	100
15	132	175	228	259	255	230	115
16	176	186	220	265	281	197	108
17	121	158	364	224	256	173	103
18	137	150	263	221	217	153	84
19	109	131	185	174	193	140	102
20	119	133	215	207	196	142	110
21	118	162	228	204	240	124	152
22	162	152	208	268	222	167	126
23	146	144	192	215	223	125	112

 

 

SELECT *
  FROM (
               --메인내용
       ) A
PIVOT (
        SUM(totalMemUvCnt)
        FOR WEEKDAY IN ([1], [2], [3], [4], [5], [6], [7])
      ) AS  B

 

 

HOURS	1	2	3	4	5	6	7
00	122	156	146	209	233	203	144
01	101	106	105	174	181	148	128
02	72	71	57	117	131	89	71
03	41	60	56	75	85	58	77
04	33	30	24	66	70	48	33
05	22	25	28	62	48	33	43
06	28	42	35	80	67	40	42
07	41	57	55	104	96	72	48
08	59	80	63	147	111	83	72
09	68	125	117	178	159	130	95
10	113	175	176	241	227	295	132
11	95	182	211	264	228	267	149
12	108	146	176	256	215	179	109
13	113	172	189	211	248	356	99
14	88	163	215	236	253	307	100
15	132	175	228	259	255	230	115
16	176	186	220	265	281	197	108
17	121	158	364	224	256	173	103
18	137	150	263	221	217	153	84
19	109	131	185	174	193	140	102
20	119	133	215	207	196	142	110
21	118	162	228	204	240	124	152
22	162	152	208	268	222	167	126
23	146	144	192	215	223	125	112

 

 

이렇게 했더니... WEEKDAY(1~7) 마다 각 시간별로 totalMemUvCnt 컬럼 SUM 한 것이 의도한 대로 위치에 들어갔다.

 

그런데... 다 풀린 줄 알았던 것도 잠시.

totalUvCnt 를 SUM 해서 피벗 B 옆에 또 피벗 C를 붙여 넣으려고 했는데

이미 피벗 B에서 WEEKDAY 컬럼을 써버려 사라지고..

 

WEEKDAY컬럼을 여러개 해야 하나.... 그럼 WEEKDAYA, WEEKDAYB, ... 이런식으로 하면 피벗을 계속 추가할 수는 있는데.

PIVOT (
        SUM(totalMemUvCnt)
        FOR WEEKDAYA IN ([1], [2], [3], [4], [5], [6], [7])
      ) AS  B
PIVOT (
        SUM(totalUvCnt)
        FOR WEEKDAYB IN ([8], [9], [10], [11], [12], [13], [14])
      ) AS  C

 

뭔가 비효율적이다...

 

CASE문을 쓰거나

WITH절 등을 활용하는 게 나을 것 같다.

 

참고

http://ntalbs.github.io/2015/sql-transpose/