데이터베이스 질문과 답변

PIVOT으로 구성할려고 하니 잘안되서요...가능할가요?... 0 2 1,383

by 가와구찌 [SQL Query] PIVOT [2020.06.18 18:32:15]

원본 데이터

일자 환자수 검사실
2020-06-01 6 검사실0
2020-06-01 74 검사실1
2020-06-01 23 검사실2
2020-06-01 4 검사실3
2020-06-01 7 검사실4
2020-06-01 9 검사실5
2020-06-01 20 검사실6
2020-06-02 5 검사실0
2020-06-02 80 검사실1
2020-06-02 20 검사실2
2020-06-02 2 검사실3
2020-06-02 6 검사실4
2020-06-02 5 검사실5
2020-06-02 15 검사실6
2020-06-03 7 검사실0
2020-06-03 83 검사실1
2020-06-03 22 검사실2
2020-06-03 2 검사실3
2020-06-03 7 검사실4
2020-06-03 3 검사실5
2020-06-03 17 검사실6

 

위의 형태를 아래와 같이 데이터를 조회하고 싶은데...

 

일자 검사실0 검사실1 검사실2 검사실3 검사실4 검사실5 검사실6
2020-06-01 6 74 23 4 7 9 20
2020-06-02 5 80 20 2 6 5 15
2020-06-03 7 83 22 2 7 3 17

 

 

혼자서 이방법 저방법 해보다가 ㅠㅠ  도저히 답이 안나와서 ..여쭤보아요..

너무 답답해서 ... 궁금하기도 하고... 좀 도와주세요.ㅠㅠ 

 

 

 

 

by ㅇㅇ준 [2020.06.18 21:30:40] 
WITH TAB AS(
            SELECT '2020-06-01' DT ,6 CNT ,'검사실0' PLC FROM DUAL UNION ALL
            SELECT '2020-06-01',74,'검사실1' FROM DUAL UNION ALL
            SELECT '2020-06-01',23,'검사실2' FROM DUAL UNION ALL
            SELECT '2020-06-01',4,'검사실3' FROM DUAL UNION ALL
            SELECT '2020-06-01',7,'검사실4' FROM DUAL UNION ALL
            SELECT '2020-06-01',9,'검사실5' FROM DUAL UNION ALL
            SELECT '2020-06-01',20,'검사실6' FROM DUAL UNION ALL
            SELECT '2020-06-02',5,'검사실0' FROM DUAL UNION ALL
            SELECT '2020-06-02',80,'검사실1' FROM DUAL UNION ALL
            SELECT '2020-06-02',20,'검사실2' FROM DUAL UNION ALL
            SELECT '2020-06-02',2,'검사실3' FROM DUAL UNION ALL
            SELECT '2020-06-02',6,'검사실4' FROM DUAL UNION ALL
            SELECT '2020-06-02',5,'검사실5' FROM DUAL UNION ALL
            SELECT '2020-06-02',15,'검사실6' FROM DUAL UNION ALL
            SELECT '2020-06-03',7,'검사실0' FROM DUAL UNION ALL
            SELECT '2020-06-03',83,'검사실1' FROM DUAL UNION ALL
            SELECT '2020-06-03',22,'검사실2' FROM DUAL UNION ALL
            SELECT '2020-06-03',2,'검사실3' FROM DUAL UNION ALL
            SELECT '2020-06-03',7,'검사실4' FROM DUAL UNION ALL
            SELECT '2020-06-03',3,'검사실5' FROM DUAL UNION ALL
            SELECT '2020-06-03',17,'검사실6' FROM DUAL 
)
SELECT *
  FROM TAB
 PIVOT ( SUM(CNT) FOR PLC IN ('검사실0', '검사실1', '검사실2', '검사실3', '검사실4', '검사실5', '검사실6') )
 ORDER BY DT;

 

주의하실 것은 검사실 리스트가 정확히 IN 안에 들어가야한다는 점입니다.

by 요런꺅쟁이 [2020.06.19 09:12:54]

감사합니다. 정말 감사해요~

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