컬럼이 400개 이하라는 가정 하에...
오너명 테이블명1 컬럼명1
오너명 테이블명1 컬럼명2
오너명 테이블명1 컬럼명3
오너명 테이블명1 컬럼명4
오너명 테이블명2 컬럼명1
오너명 테이블명2 컬럼명2
오너명 테이블명2 컬럼명3
오너명 테이블명2 컬럼명4
오너명 테이블명2 컬럼명5
오너명 테이블명2 컬럼명6
.
.
.
이런 걸
오너명 테이블명1 컬럼명1 컬럼명2 컬럼명3 컬럼명4
오너명 테이블명2 컬럼명1 컬럼명2 컬럼명3 컬럼명4 컬럼명5 컬럼명6
이렇게 나오게 하는 예제
WITH COLUMNS AS
(
SELECT OWNER, TABLE_NAME, COLUMN_NAME||' '|| CASE WHEN DATA_TYPE = 'NUMBER' AND DATA_PRECISION IS NOT NULL AND DATA_SCALE = 0 THEN DATA_TYPE || '(' || DATA_PRECISION || ')'
WHEN DATA_TYPE = 'NUMBER' AND DATA_PRECISION IS NOT NULL AND DATA_SCALE IS NOT NULL THEN DATA_TYPE || '(' || DATA_PRECISION || ',' || DATA_SCALE || ')'
WHEN DATA_TYPE = 'NUMBER' AND DATA_PRECISION IS NOT NULL AND DATA_SCALE IS NULL THEN DATA_TYPE || '(' || DATA_PRECISION || ')'
WHEN DATA_TYPE = 'NUMBER' AND DATA_PRECISION IS NULL THEN DATA_TYPE
ELSE DATA_TYPE || '(' || DATA_LENGTH || ')'
END AS COLUMN_NAME
, COLUMN_ID
FROM DBA_TAB_COLS
WHERE OWNER = '오너다'
)
SELECT * FROM COLUMNS
PIVOT ( MAX(COLUMN_NAME) FOR COLUMN_ID IN (1 AS COL1, 2 AS COL2, 3 AS COL3, 4 AS COL4, 5 AS COL5, 6 AS COL6, 7 AS COL7, 8 AS COL8, 9 AS COL9, 10 AS COL10, 11 AS COL11, 12 AS COL12, 13 AS COL13, 14 AS COL14, 15 AS COL15, 16 AS COL16, 17 AS COL17, 18 AS COL18, 19 AS COL19, 20 AS COL20, 21 AS COL21, 22 AS COL22, 23 AS COL23, 24 AS COL24, 25 AS COL25, 26 AS COL26, 27 AS COL27, 28 AS COL28, 29 AS COL29, 30 AS COL30, 31 AS COL31, 32 AS COL32, 33 AS COL33, 34 AS COL34, 35 AS COL35, 36 AS COL36, 37 AS COL37, 38 AS COL38, 39 AS COL39, 40 AS COL40, 41 AS COL41, 42 AS COL42, 43 AS COL43, 44 AS COL44, 45 AS COL45, 46 AS COL46, 47 AS COL47, 48 AS COL48, 49 AS COL49, 50 AS COL50, 51 AS COL51, 52 AS COL52, 53 AS COL53, 54 AS COL54, 55 AS COL55, 56 AS COL56, 57 AS COL57, 58 AS COL58, 59 AS COL59, 60 AS COL60, 61 AS COL61, 62 AS COL62, 63 AS COL63, 64 AS COL64, 65 AS COL65, 66 AS COL66, 67 AS COL67, 68 AS COL68, 69 AS COL69, 70 AS COL70, 71 AS COL71, 72 AS COL72, 73 AS COL73, 74 AS COL74, 75 AS COL75, 76 AS COL76, 77 AS COL77, 78 AS COL78, 79 AS COL79, 80 AS COL80, 81 AS COL81, 82 AS COL82, 83 AS COL83, 84 AS COL84, 85 AS COL85, 86 AS COL86, 87 AS COL87, 88 AS COL88, 89 AS COL89, 90 AS COL90, 91 AS COL91, 92 AS COL92, 93 AS COL93, 94 AS COL94, 95 AS COL95, 96 AS COL96, 97 AS COL97, 98 AS COL98, 99 AS COL99, 100 AS COL100, 101 AS COL101, 102 AS COL102, 103 AS COL103, 104 AS COL104, 105 AS COL105, 106 AS COL106, 107 AS COL107, 108 AS COL108, 109 AS COL109, 110 AS COL110, 111 AS COL111, 112 AS COL112, 113 AS COL113, 114 AS COL114, 115 AS COL115, 116 AS COL116, 117 AS COL117, 118 AS COL118, 119 AS COL119, 120 AS COL120, 121 AS COL121, 122 AS COL122, 123 AS COL123, 124 AS COL124, 125 AS COL125, 126 AS COL126, 127 AS COL127, 128 AS COL128, 129 AS COL129, 130 AS COL130, 131 AS COL131, 132 AS COL132, 133 AS COL133, 134 AS COL134, 135 AS COL135, 136 AS COL136, 137 AS COL137, 138 AS COL138, 139 AS COL139, 140 AS COL140, 141 AS COL141, 142 AS COL142, 143 AS COL143, 144 AS COL144, 145 AS COL145, 146 AS COL146, 147 AS COL147, 148 AS COL148, 149 AS COL149, 150 AS COL150, 151 AS COL151, 152 AS COL152, 153 AS COL153, 154 AS COL154, 155 AS COL155, 156 AS COL156, 157 AS COL157, 158 AS COL158, 159 AS COL159, 160 AS COL160, 161 AS COL161, 162 AS COL162, 163 AS COL163, 164 AS COL164, 165 AS COL165, 166 AS COL166, 167 AS COL167, 168 AS COL168, 169 AS COL169, 170 AS COL170, 171 AS COL171, 172 AS COL172, 173 AS COL173, 174 AS COL174, 175 AS COL175, 176 AS COL176, 177 AS COL177, 178 AS COL178, 179 AS COL179, 180 AS COL180, 181 AS COL181, 182 AS COL182, 183 AS COL183, 184 AS COL184, 185 AS COL185, 186 AS COL186, 187 AS COL187, 188 AS COL188, 189 AS COL189, 190 AS COL190, 191 AS COL191, 192 AS COL192, 193 AS COL193, 194 AS COL194, 195 AS COL195, 196 AS COL196, 197 AS COL197, 198 AS COL198, 199 AS COL199, 200 AS COL200, 201 AS COL201, 202 AS COL202, 203 AS COL203, 204 AS COL204, 205 AS COL205, 206 AS COL206, 207 AS COL207, 208 AS COL208, 209 AS COL209, 210 AS COL210, 211 AS COL211, 212 AS COL212, 213 AS COL213, 214 AS COL214, 215 AS COL215, 216 AS COL216, 217 AS COL217, 218 AS COL218, 219 AS COL219, 220 AS COL220, 221 AS COL221, 222 AS COL222, 223 AS COL223, 224 AS COL224, 225 AS COL225, 226 AS COL226, 227 AS COL227, 228 AS COL228, 229 AS COL229, 230 AS COL230, 231 AS COL231, 232 AS COL232, 233 AS COL233, 234 AS COL234, 235 AS COL235, 236 AS COL236, 237 AS COL237, 238 AS COL238, 239 AS COL239, 240 AS COL240, 241 AS COL241, 242 AS COL242, 243 AS COL243, 244 AS COL244, 245 AS COL245, 246 AS COL246, 247 AS COL247, 248 AS COL248, 249 AS COL249, 250 AS COL250, 251 AS COL251, 252 AS COL252, 253 AS COL253, 254 AS COL254, 255 AS COL255, 256 AS COL256, 257 AS COL257, 258 AS COL258, 259 AS COL259, 260 AS COL260, 261 AS COL261, 262 AS COL262, 263 AS COL263, 264 AS COL264, 265 AS COL265, 266 AS COL266, 267 AS COL267, 268 AS COL268, 269 AS COL269, 270 AS COL270, 271 AS COL271, 272 AS COL272, 273 AS COL273, 274 AS COL274, 275 AS COL275, 276 AS COL276, 277 AS COL277, 278 AS COL278, 279 AS COL279, 280 AS COL280, 281 AS COL281, 282 AS COL282, 283 AS COL283, 284 AS COL284, 285 AS COL285, 286 AS COL286, 287 AS COL287, 288 AS COL288, 289 AS COL289, 290 AS COL290, 291 AS COL291, 292 AS COL292, 293 AS COL293, 294 AS COL294, 295 AS COL295, 296 AS COL296, 297 AS COL297, 298 AS COL298, 299 AS COL299, 300 AS COL300, 301 AS COL301, 302 AS COL302, 303 AS COL303, 304 AS COL304, 305 AS COL305, 306 AS COL306, 307 AS COL307, 308 AS COL308, 309 AS COL309, 310 AS COL310, 311 AS COL311, 312 AS COL312, 313 AS COL313, 314 AS COL314, 315 AS COL315, 316 AS COL316, 317 AS COL317, 318 AS COL318, 319 AS COL319, 320 AS COL320, 321 AS COL321, 322 AS COL322, 323 AS COL323, 324 AS COL324, 325 AS COL325, 326 AS COL326, 327 AS COL327, 328 AS COL328, 329 AS COL329, 330 AS COL330, 331 AS COL331, 332 AS COL332, 333 AS COL333, 334 AS COL334, 335 AS COL335, 336 AS COL336, 337 AS COL337, 338 AS COL338, 339 AS COL339, 340 AS COL340, 341 AS COL341, 342 AS COL342, 343 AS COL343, 344 AS COL344, 345 AS COL345, 346 AS COL346, 347 AS COL347, 348 AS COL348, 349 AS COL349, 350 AS COL350, 351 AS COL351, 352 AS COL352, 353 AS COL353, 354 AS COL354, 355 AS COL355, 356 AS COL356, 357 AS COL357, 358 AS COL358, 359 AS COL359, 360 AS COL360, 361 AS COL361, 362 AS COL362, 363 AS COL363, 364 AS COL364, 365 AS COL365, 366 AS COL366, 367 AS COL367, 368 AS COL368, 369 AS COL369, 370 AS COL370, 371 AS COL371, 372 AS COL372, 373 AS COL373, 374 AS COL374, 375 AS COL375, 376 AS COL376, 377 AS COL377, 378 AS COL378, 379 AS COL379, 380 AS COL380, 381 AS COL381, 382 AS COL382, 383 AS COL383, 384 AS COL384, 385 AS COL385, 386 AS COL386, 387 AS COL387, 388 AS COL388, 389 AS COL389, 390 AS COL390, 391 AS COL391, 392 AS COL392, 393 AS COL393, 394 AS COL394, 395 AS COL395, 396 AS COL396, 397 AS COL397, 398 AS COL398, 399 AS COL399, 400 AS COL400)
)
잘 해석을 해보면
PIVOT ( MAX(COLUMN_NAME) FOR COLUMN_ID IN (1 AS COL1
주황색 부분은 PIVOT해서 보여주고 싶은 열을 지정
파란색 부분은 PIVOT해서 보여주고 싶은 열을 어떤 순으로 보여줄 지 기준(컬럼)을 지정.
안까먹으려고 다시 기록함.
수십번을 써도... 한동안 안쓰면 자꾸 까먹네. ㅠㅠ