데이터베이스 질문과 답변
구글링해서 보면 유사한 내용들은 많은데 저와 같은 경우는 없는것 같아서 조언을 구해봅니다.
보통은 값을 가지고 처리하다 보니 max, min을 사용하는 예제들이더라구요
질문 : 아래와 같이 리스트들이 있습니다.
1 aaaaa
2 bbbbb
3 asdfadsf
4 ccccc
5 aaaddd
6 sdf89f
위의 세로 리스트를 아래와 같이 결과가 출력되게 하고 싶은데 어떻게 하면 좋을지요
| 컬럼1 | 컬럼2 | 컬럼3 | 컬럼4 | |
| 1 | aaaaa | bbbbb | asdfadsf | ccccc |
| 2 | aaaddd | sdf89f | ||
| 3 | ||||
| 4 |
-------------------------------------------
두 번째 입니다.
문자열 목록이 가나다 순으로 되어 있다고 했을 때 출력하는 순서는 아래와 같이 나오게 하고 싶습니다.
쿼리 자체로 방법이 있을 수 있는지용
| 컬럼1 | 컬럼2 | 컬럼 | 컬럼4 | |
| 1 | aaa | eee | iii | mmm |
| 2 | bbb | fff | jjj | maa |
| 3 | ccc | ggg | kkk | lss |
| 4 | ddd | hhh | lllte | tea |
by 임상준
[2017.04.26 09:02:03]
1번은 숫자만 그룹 가공 잘 하면 될 것 같은데요,, 더 효율적인 방법이 있을지
with t as (
select 1 as id, 'aaaaa' as val from dual union all
select 2 as id, 'bbbbb' as val from dual union all
select 3 as id, 'asdfadsf' as val from dual union all
select 4 as id, 'ccccc' as val from dual union all
select 5 as id, 'aaaddd' as val from dual union all
select 6 as id, 'sdf89f' as val from dual
)
select
row_grp
, max(case when col_grp = 1 then val end) as grp_1
, max(case when col_grp = 2 then val end) as grp_2
, max(case when col_grp = 3 then val end) as grp_3
, max(case when col_grp = 0 then val end) as grp_4
from (
select
id
,mod(id, 4) as col_grp
,ROW_NUMBER() OVER(PARTITION BY MOD(ID, 4) ORDER BY ID) as row_grp
, val
from t
)
group by row_grp
;
by 마농
[2017.04.26 10:08:49]
WITH t AS
(
SELECT 1 seq, 'aaaaa' v FROM dual
UNION ALL SELECT 2, 'bbbbb' FROM dual
UNION ALL SELECT 3, 'asdfadsf' FROM dual
UNION ALL SELECT 4, 'ccccc' FROM dual
UNION ALL SELECT 5, 'aaaddd' FROM dual
UNION ALL SELECT 6, 'sdf89f' FROM dual
)
SELECT *
FROM (SELECT v
, MOD(ROWNUM-1, 4)+1 x
, CEIL(ROWNUM / 4) y
FROM (SELECT seq, v FROM t ORDER BY seq)
)
PIVOT (MIN(v) FOR x IN (1, 2, 3, 4))
ORDER BY y
;
WITH t AS
(
SELECT 'aaa' v FROM dual
UNION ALL SELECT 'bbb' FROM dual
UNION ALL SELECT 'ccc' FROM dual
UNION ALL SELECT 'ddd' FROM dual
UNION ALL SELECT 'eee' FROM dual
UNION ALL SELECT 'fff' FROM dual
UNION ALL SELECT 'ggg' FROM dual
UNION ALL SELECT 'hhh' FROM dual
UNION ALL SELECT 'iii' FROM dual
UNION ALL SELECT 'jjj' FROM dual
UNION ALL SELECT 'kkk' FROM dual
UNION ALL SELECT 'lllte' FROM dual
UNION ALL SELECT 'lss' FROM dual
UNION ALL SELECT 'mmm' FROM dual
UNION ALL SELECT 'maa' FROM dual
UNION ALL SELECT 'tea' FROM dual
)
SELECT *
FROM (SELECT v
, x
, ROW_NUMBER() OVER(PARTITION BY x ORDER BY v) y
FROM (SELECT v
, NTILE(4) OVER(ORDER BY v) x
FROM t
)
)
PIVOT (MIN(v) FOR x IN (1, 2, 3, 4))
ORDER BY y
;
) 버튼을 클릭하여 작성 하시면 됩니다.