꿈꾸는 개발자, DBA 커뮤니티 구루비

2개 조건으로 열로 변경하고 싶습니다. mariadb

by tosswin [SQL Query] mariadb query 행->열 [2023.10.10 10:58:22]

좀 길기는 하지만 다음과 같이 테이블에 저장되어 있습니다.

mesDate	type(varchar)	content	status
2023-02-12	0	hp1	NG
2023-02-12	0	hp2	OK
2023-02-12	0	st1	OK
2023-02-12	0	st2	OK
2023-02-12	1	e1	OK
2023-02-12	1	h1	OK
2023-02-12	1	h2	NG
2023-02-12	1	h3	OK
2023-02-12	1	w1	OK
2023-02-12	2	e1	OK
2023-02-12	2	h1	OK
2023-02-12	2	h2	OK
2023-02-12	2	h3	OK
2023-02-12	2	w1	OK
2023-02-13	0	hp1	OK
2023-02-13	0	hp2	OK
2023-02-13	0	st1	OK
2023-02-13	0	st2	OK
2023-02-13	1	e1	OK
2023-02-13	1	h1	OK
2023-02-13	1	h2	OK
2023-02-13	1	h3	OK
2023-02-13	1	w1	OK
2023-02-13	2	e1	OK
2023-02-13	2	h1	OK
2023-02-13	2	h2	OK
2023-02-13	2	h3	OK
2023-02-13	2	w1	OK
2023-02-14	0	hp1	OK
2023-02-14	0	hp2	OK
2023-02-14	0	st1	OK
2023-02-14	0	st2	OK
2023-02-14	1	e1	OK
2023-02-14	1	h1	OK
2023-02-14	1	h2	OK
2023-02-14	1	h3	OK
2023-02-14	1	w1	OK
2023-02-14	2	e1	OK
2023-02-14	2	h1	OK
2023-02-14	2	h2	OK
2023-02-14	2	h3	OK
2023-02-14	2	w1	OK
2023-02-15	0	hp1	OK
2023-02-15	0	hp2	OK
2023-02-15	0	st1	NG
2023-02-15	0	st2	OK
2023-02-15	1	e1	OK
2023-02-15	1	h1	OK
2023-02-15	1	h2	OK
2023-02-15	1	h3	OK
2023-02-15	1	w1	OK
2023-02-15	2	e1	OK
2023-02-15	2	h1	OK
2023-02-15	2	h2	OK
2023-02-15	2	h3	OK
2023-02-15	2	w1	OK

제가 변경하고 싶은 결과는 다음과 같습니다.

mesDate

0_hp1

0_hp2

0_st1

0_st2

1_e1

1_h1

1_h2

1_h3

1_w1

2_e1

2_h1

2_h2

2_h3

2_w1

2023-02-12

2023-02-13

2023-02-14

2023-02-15

이렇게 변경하려면 query를 어떻게 작성해야 할까요?

도저희 떠오르지 않아 이렇게 질문드립니다.

감사합니다.

by 마농 [2023.10.10 11:31:53]

SELECT mesDate
     , MIN(CASE WHEN type = '0' AND content = 'hp1' THEN status END) "0_hp1"
     , MIN(CASE WHEN type = '0' AND content = 'hp2' THEN status END) "0_hp2"
     , MIN(CASE WHEN type = '0' AND content = 'st1' THEN status END) "0_st1"
     , MIN(CASE WHEN type = '0' AND content = 'st2' THEN status END) "0_st2"
     , MIN(CASE WHEN type = '1' AND content = 'e1'  THEN status END) "1_e1"
     , MIN(CASE WHEN type = '1' AND content = 'h1'  THEN status END) "1_h1"
     , MIN(CASE WHEN type = '1' AND content = 'h2'  THEN status END) "1_h2"
     , MIN(CASE WHEN type = '1' AND content = 'h3'  THEN status END) "1_h3"
     , MIN(CASE WHEN type = '1' AND content = 'w1'  THEN status END) "1_w1"
     , MIN(CASE WHEN type = '2' AND content = 'e1'  THEN status END) "2_e1"
     , MIN(CASE WHEN type = '2' AND content = 'h1'  THEN status END) "2_h1"
     , MIN(CASE WHEN type = '2' AND content = 'h2'  THEN status END) "2_h2"
     , MIN(CASE WHEN type = '2' AND content = 'h3'  THEN status END) "2_h3"
     , MIN(CASE WHEN type = '2' AND content = 'w1'  THEN status END) "2_w1"
  FROM t
 GROUP BY mesDate
;

SELECT mesDate
     , MIN(CASE x WHEN '0_hp1' THEN s END) "0_hp1"
     , MIN(CASE x WHEN '0_hp2' THEN s END) "0_hp2"
     , MIN(CASE x WHEN '0_st1' THEN s END) "0_st1"
     , MIN(CASE x WHEN '0_st2' THEN s END) "0_st2"
     , MIN(CASE x WHEN '1_e1'  THEN s END) "1_e1"
     , MIN(CASE x WHEN '1_h1'  THEN s END) "1_h1"
     , MIN(CASE x WHEN '1_h2'  THEN s END) "1_h2"
     , MIN(CASE x WHEN '1_h3'  THEN s END) "1_h3"
     , MIN(CASE x WHEN '1_w1'  THEN s END) "1_w1"
     , MIN(CASE x WHEN '2_e1'  THEN s END) "2_e1"
     , MIN(CASE x WHEN '2_h1'  THEN s END) "2_h1"
     , MIN(CASE x WHEN '2_h2'  THEN s END) "2_h2"
     , MIN(CASE x WHEN '2_h3'  THEN s END) "2_h3"
     , MIN(CASE x WHEN '2_w1'  THEN s END) "2_w1"
  FROM (SELECT mesDate
             , CONCAT(type, '_', content) x
             , status s
          FROM t
        ) a
 GROUP BY mesDate
;

by tosswin [2023.10.10 12:13:34]

마농님 또 감사드립니다.

마농님의 능력이 부럽습니다 ^^;

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

) 버튼을 클릭하여 작성 하시면 됩니다.

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 질문과 답변