by 김민희 [SQL Query] oracle mssql [2018.03.05 19:57:56]
SELECT
ROW_NUMBER() OVER (ORDER BY s.emerg_no DESC) RN,
s.emerg_no emergNo,
s.biz_place_no bizPlaceNo,
s.emerg_occur_place emergOccurPlace,
s.emerg_category emergCategory,
s.accd_content accdContent,
s.act_situation actSituation,
s.issue_person_no issuePersonNo,
s.issue_date issueDate,
s.close_person_no closePersonNo,
s.close_date closeDate,
s.emerg_status emergStatus,
s.reg_id regId,
s.reg_date regDate,
s.upd_id updId,
s.upd_date updDate,
s.reg_dept_no regDeptNo,
e1.name issuePersonName,
e2.name closePersonName,
(
SELECT SUBSTR(MAX(SYS_CONNECT_BY_PATH(r.chemicals_name_kor, ',')), 2)
FROM (SELECT s1.*, ROW_NUMBER() OVER (PARTITION BY emerg_no ORDER BY leak_chemicals_no ASC) RN FROM seh_leak_chemicals s1) s1
LEFT JOIN seh_chemicals c ON s1.chemicals_no = c.chemicals_no
LEFT JOIN seh_chemicals_req r ON c.chemicals_req_no = r.chemicals_req_no
WHERE s1.emerg_no = s.emerg_no
START WITH s1.emerg_no = s.emerg_no AND RN = 1
CONNECT BY PRIOR RN = RN -1
) leakChemicalsNamesKor
FROM
seh_emerg s
LEFT JOIN seh_emp e1 ON s.issue_person_no = e1.emp_seq
LEFT JOIN seh_emp e2 ON s.close_person_no = e2.emp_seq
현재 오라클로 되어있는데 mssql로 변환하는게 너무 어려워서 도움 부탁드립니다.
기존 Oracle 쿼리도 옛날 방식이네요.
11G 라면 ListAgg 로 바꾸시는게 좋습니다.
http://gurubee.net/article/55512
SELECT ROW_NUMBER() OVER(ORDER BY s.emerg_no DESC) rn , s.emerg_no emergNo , s.biz_place_no bizPlaceNo , s.emerg_occur_place emergOccurPlace , s.emerg_category emergCategory , s.accd_content accdContent , s.act_situation actSituation , s.issue_person_no issuePersonNo , s.issue_date issueDate , s.close_person_no closePersonNo , s.close_date closeDate , s.emerg_status emergStatus , s.reg_id regId , s.reg_date regDate , s.upd_id updId , s.upd_date updDate , s.reg_dept_no regDeptNo , e1.name issuePersonName , e2.name closePersonName , STUFF( (SELECT ',' + r.chemicals_name_kor FROM seh_leak_chemicals s1 LEFT JOIN seh_chemicals c ON s1.chemicals_no = c.chemicals_no LEFT JOIN seh_chemicals_req r ON c.chemicals_req_no = r.chemicals_req_no WHERE s1.emerg_no = s.emerg_no ORDER BY s1.leak_chemicals_no FOR XML PATH('') ), 1, 1, '') leakChemicalsNamesKor FROM seh_emerg s LEFT JOIN seh_emp e1 ON s.issue_person_no = e1.emp_seq LEFT JOIN seh_emp e2 ON s.close_person_no = e2.emp_seq ;