시험종목의 테이블 PK값 하나 EX) A01 , 명칭 컬럼하나 EX)행정고시
합격검사 테이블 PK값 하나 , 시험종목 FK EX) A01, 합격여부 Y,N, 시험장소 EX) PLACE001, 시험일자 Ex)20200824
시험장소 테이블 PK값 하나 , 시험장소
이렇게 3테이블이 있을때
시험종목은 조건없이 모두 검색되는 상태에서
오른쪽에 한 컬럼으로 시험일자 20200824 일때
(시험장 - 합격여부)
,(시험장2 - 합격여부)
,(시험장3 - 합격여부)
3개 값이 하나의 row로 뜨게하고 싶습니다.
한번 중첩으로 시험장 - 합격여부는 만들었으나 그 다음 2번째 중첩을 못하고 있습니다...
조언을 얻고자 합니다.
네 MSSQL 입니다. 왠지 SQL 짜다 보니까 계속 되돌이표가 되는 느낌이라.. SELECT CCD_02_S ,A.T6 ,A.T7 ,B.CCD_02 AS CCD_M FROM (SELECT DISTINCT (SELECT CLT_02+' - '+CASE OQC_10 WHEN 'Y' THEN'합격'WHEN 'N' THEN'불합격' ELSE '대기' END FROM T_REQ LEFT OUTER JOIN T_CCD ON CCD_01 = A.OQC_08 LEFT OUTER JOIN T_CLT ON CLT_01 = REQ_03 WHERE OQC_07 = (SELECT DATEADD(DAY,6- DATEPART(DW,'20200824'),CONVERT(date,'20200824'))) AND REQ_01 = A.OQC_03 FOR XML PATH('')) AS T6 , (SELECT CLT_02+' - '+CASE OQC_10 WHEN 'Y' THEN'합격'WHEN 'N' THEN'불합격' ELSE '대기' END FROM T_REQ LEFT OUTER JOIN T_CCD ON CCD_01 = A.OQC_08 LEFT OUTER JOIN T_CLT ON CLT_01 = REQ_03 WHERE OQC_07 = (SELECT DATEADD(DAY,7- DATEPART(DW,'20200824'),CONVERT(date,'20200824'))) AND REQ_01 = A.OQC_03 FOR XML PATH('')) AS T7 ,CCD_02 AS CCD_02_S ,CCD_03 FROM T_CCD LEFT OUTER JOIN T_OQC A ON A.OQC_ID = CCD_ID AND A.OQC_08 = CCD_01 AND (A.OQC_07 BETWEEN (SELECT DATEADD(DAY,1- DATEPART(DW,'20200824'),CONVERT(date,'20200824'))) AND (SELECT DATEADD(DAY,7- DATEPART(DW,'20200824'),CONVERT(date,'20200824')))) WHERE 1=1 AND CCD_03 != '') A LEFT OUTER JOIN T_CCD B ON A.CCD_03 = B.CCD_01
SELECT STRING_AGG(CLT_02+'+'+OQC_10,'--') AS T6 FROM T_CCD INNER JOIN T_OQC ON OQC_08 = CCD_01 LEFT OUTER JOIN T_REQ ON REQ_01 = OQC_03 LEFT OUTER JOIN T_CLT ON REQ_03 = CLT_01 WHERE CCD_03 != '' AND OQC_07 = (SELECT DATEADD(DAY,6- DATEPART(DW,'20200824'),CONVERT(date,'20200824'))) GROUP BY CCD_02 이렇게 하루는 금방 생각이 나는데 주간 일정을 뽑을때 막힙니다..
버전은 mssql - 2019입니다.
SELECT a.ccd_02 , STRING_AGG(CASE DATEPART(DW, b.oqc_07) WHEN 1 THEN d.clt_02 + '+' + b.oqc_10 END, '--') t1 , STRING_AGG(CASE DATEPART(DW, b.oqc_07) WHEN 2 THEN d.clt_02 + '+' + b.oqc_10 END, '--') t2 , STRING_AGG(CASE DATEPART(DW, b.oqc_07) WHEN 3 THEN d.clt_02 + '+' + b.oqc_10 END, '--') t3 , STRING_AGG(CASE DATEPART(DW, b.oqc_07) WHEN 4 THEN d.clt_02 + '+' + b.oqc_10 END, '--') t4 , STRING_AGG(CASE DATEPART(DW, b.oqc_07) WHEN 5 THEN d.clt_02 + '+' + b.oqc_10 END, '--') t5 , STRING_AGG(CASE DATEPART(DW, b.oqc_07) WHEN 6 THEN d.clt_02 + '+' + b.oqc_10 END, '--') t6 , STRING_AGG(CASE DATEPART(DW, b.oqc_07) WHEN 7 THEN d.clt_02 + '+' + b.oqc_10 END, '--') t7 FROM t_ccd a INNER JOIN t_oqc b ON b.oqc_08 = a.ccd_01 LEFT OUTER JOIN t_req c ON c.req_01 = b.oqc_03 LEFT OUTER JOIN t_clt d ON c.req_03 = d.clt_01 WHERE a.ccd_03 != '' AND b.oqc_07 >= DATEADD(DAY, 1 - DATEPART(DW, '20200824'), CONVERT(date, '20200824')) AND b.oqc_07 <= DATEADD(DAY, 7 - DATEPART(DW, '20200824'), CONVERT(date, '20200824')) GROUP BY a.ccd_02 ;