10.데이터베이스

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

 

1
2
3
4
5
6
7
8
9
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)
 cs

 

 

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

 

 

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

 

 

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, ... 이런식으로 하면 피벗을 계속 추가할 수는 있는데.

1
2
3
4
5
6
7
8
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
 cs

 

뭔가 비효율적이다...

 

CASE문을 쓰거나

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

 

참고

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

+ Recent posts

티스토리툴바