고수님 쿼리 짤려고 하는데 너무헤메고 있네요 0 3 810

by K-ART [SQL Query] [2017.03.30 20:21:14]


고수님 도움이 필요합니다  이쿼리가 가능한가요?


테이블


  A        B         C        D        E


  a         1         2         3       4


  a         5        6         7       8


  a         9        10      11      12


  b        13       14      15      16


  b        17       18       19      20       


  b         21      22       23      24


--------------------------------------------------결과------------------------------------------------------------------------------------------------------------------------------------


 A     B     C      D     E     F      G      H       I       J


 a      1     2     3     4     b      13     14      15     16


 a      5     6     7     8     b      17     18      19     20
 
 
 a      9    10     11    12    b      21     22      23     24


  

 

by jkson [2017.03.31 08:08:34]
with t as
(
select 'a' a, 1  b, 2  c, 3  d,  4  e from dual union all
select 'a' a, 5  b, 6  c, 7  d,  8  e from dual union all
select 'a' a, 9  b, 10 c, 11 d,  12 e from dual union all
select 'b' a, 13 b, 14 c, 15 d,  16 e from dual union all
select 'b' a, 17 b, 18 c, 19 d,  20 e from dual union all
select 'b' a, 21 b, 22 c, 23 d,  24 e from dual
)
select gp1_a a, gp1_b b, gp1_c c, gp1_d d, gp1_e e
      ,gp2_a f, gp2_b g, gp2_c h, gp2_d i, gp2_e j
from
(
select dense_rank() over(order by a) gp1
     , row_number() over(partition by a order by b) gp2
     , t.* 
  from t
) 
pivot (max(a) a, max(b) b, max(c) c, max(d) d, max(e) e for gp1 in (1 gp1,2 gp2))

--다른 방법
select max(a) a, max(b) b, max(c) c, max(d) d, max(e) e
     , max(f) f, max(g) g, max(h) h, max(i) i, max(j) j
  from
    (  
    select row_number() over(order by b) rn, a, b, c, d, e
          ,null f, null g, null h, null i, null j
      from t
     where a = 'a'
    union all 
    select row_number() over(order by b) rn ,null, null, null, null, null
         , a, b, c, d, e
      from t
     where a = 'b'
    )
group by rn 

딱 표가 저런 형식으로만 나오는 건가요?

기준을 정확히 적어주시면 더 좋았을 것 같은데..


by 마농 [2017.03.31 08:14:18]
WITH t AS
(
SELECT 'a' a, 1 b, 2 c, 3 d, 4 e FROM dual
UNION ALL SELECT 'a',  5,  6,  7,  8 FROM dual
UNION ALL SELECT 'a',  9, 10, 11, 12 FROM dual
UNION ALL SELECT 'b', 13, 14, 15, 16 FROM dual
UNION ALL SELECT 'b', 17, 18, 19, 20 FROM dual
UNION ALL SELECT 'b', 21, 22, 23, 24 FROM dual
)
SELECT *
  FROM (SELECT a, b, c, d, e
             , ROW_NUMBER() OVER(PARTITION BY a ORDER BY b) rn
          FROM t
        )
 PIVOT(MIN(a) a, MIN(b) b, MIN(c) c, MIN(d) d, MIN(e) e FOR a IN ('a' a, 'b' b))
;

 


by swlee [2017.03.31 13:44:58]
with t as
(
select 'a' a, 1  b, 2  c, 3  d,  4  e from dual union all
select 'a' a, 5  b, 6  c, 7  d,  8  e from dual union all
select 'a' a, 9  b, 10 c, 11 d,  12 e from dual union all
select 'b' a, 13 b, 14 c, 15 d,  16 e from dual union all
select 'b' a, 17 b, 18 c, 19 d,  20 e from dual union all
select 'b' a, 21 b, 22 c, 23 d,  24 e from dual
)
select max(decode(gb,1,a)) a,max(decode(gb,1,b)) b,max(decode(gb,1,c)) c,max(decode(gb,1,d)) d,max(decode(gb,1,e)) e 
     , max(decode(gb,2,a)) f,max(decode(gb,2,b)) g,max(decode(gb,2,c)) h,max(decode(gb,2,d)) i,max(decode(gb,2,e)) j
from (
select dense_rank() over(order by a asc) gb
      , row_number() over(partition by a order by rownum) gb2
      , t.*
from t)
group by gb2 
;

 

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입