데이터베이스 질문과 답변
안녕하세요! MSSQL과 C#으로 제조업체 현장 및 관리화면 개발하는 신입개발자 입니다! 밑에와 같은 사진처럼 테이블 조회를 해야하는데요 (실제 테이블 구조와 동일하게 만들었습니다) [질문 배경] 기존 테이블 같은 경우에는 단순 조회로 SELCT ~ FROM WHERE 문으로 JOIN해서 실행하였습니다. 변경결과테이블을 위해 제가 생각한 방법은 총 두가지였는데요 1. 동적 PIVOT 2. 동적 CURSOR 동적으로 쿼리를 생성하는 이유는 1. 학번에 따라 조회될 학생이 다름 2. 학생별 입시 항목 개수별로 COLUMN이 생성되어야함 입니다. [쿼리 질문] 1. 동적 PIVOT 기존에 열로 가지고 있던 컬럼을 행으로 바꾸기위해 사용했지만 피벗대상컬럼안에 들어갈 컬럼이 4개가 들어가야하므로 방법을 몰라서 하다가 멈췄습니다. 2. 동적 CUSOR 입시항목 개수별로 COLUMN이 생성되어야 하므로 조회되어야 할 [입시방법, 원서접수,원서비,결과]컬럼을 SET해주었습니다. DECLARE @QUERY varchar(MAX) = 'SELECT ' SET @QUERY = @QUERY + '학번= ' + '''' + @학번+ '''' DECLARE CUR CURSOR FOR SELECT 학생이름 ,입학일자 ,졸업일자 , 평균성적 , 출석일 , 담임이름 , 입시항목 , 입시방법 , 원서접수 , 원서비 , 결과 FROM 학생테이블 JOIN 대학테이블 WHERE 입학일자 >= '2015-01-01' OPEN CUR FETCH NEXT FROM CUR INTO @학생이름, @입학일자, @졸업일자, @평균성적, @출석일, @담임이름, @입시항목,@입시방법, @원서접수, @원서비, @결과 WHILE @@FETCH_STATUS = 0 BEGIN SET @QUERY = @QUERY + ', ' + @학번 + '_학생이름 = ' + '''' + ISNULL(@학생이름, '') + '''' SET @QUERY = @QUERY + ', ' + @학번 + '_입학일자 = ' + '''' + ISNULL(@입학일자, '') + '''' SET @QUERY = @QUERY + ', ' + @학번 + '_졸업일자 = ' + '''' + ISNULL(@졸업일자, '') + '''' SET @QUERY = @QUERY + ', ' + @학번 + '_평균성적 = ' + '''' + ISNULL(@평균성적, '') + '''' SET @QUERY = @QUERY + ', ' + @학번 + '_출석일 = ' + '''' + ISNULL(@출석일, '') + '''' SET @QUERY = @QUERY + ', ' + @학번 + '_담임이름 = ' + '''' + ISNULL(@담임이름, '') + '''' SET @QUERY = @QUERY + ', ' + @학번 + '_입시항목 = ' + '''' + ISNULL(@입시항목, '') + '''' SET @QUERY = @QUERY + ', ' + @학번 + '_입시방법 = ' + '''' + ISNULL(@입시방법, '') + '''' SET @QUERY = @QUERY + ', ' + @학번 + '_원서접수 = ' + '''' + ISNULL(@원서접수, '') + '''' SET @QUERY = @QUERY + ', ' + @학번 + '_원서비 = ' + '''' + ISNULL(@원서비, '') + '''' SET @QUERY = @QUERY + ', ' + @학번 + '_결과 = ' + '''' + ISNULL(@결과, '') + '''' FETCH NEXT FROM CUR INTO @학생이름, @입학일자, @졸업일자, @평균성적, @출석일, @담임이름, @입시항목,@입시방법, @원서접수, @원서비, @결과 END CLOSE CUR DEALLOCATE CUR SELECT @QUERY EXEC(@QUERY)
이렇게 했을 때에 VARCHAR(MAX)용량이 초과되어 조회되지 않습니다 ㅜㅜ
CURSOR가 필요한 컬럼은 정작 [입시항목, 입시방법, 원서접수, 원서비, 결과]뿐이고
이 컬럼들만 CURSOR로 조회할 시 데이터가 잘 받아집니다.
하지만 [학생이름, 입학일자, 졸업일자, 평균성적, 출석일, 담임이름]의 데이터조회는 되지 않고
쿼리를 모드로 나눠서 파라미터로 view단에 받아오니 동일한 controlview를 사용하기 때문에
코드상에서 쿼리를 담은 table하나는 묻히게 되어 결과가 제대로 나오지 않는 상황입니다..
정적쿼리로 무식하게 때려 박을 수 있지만 언제든 입시항목은 늘어날 수 있는 상황이기 때문에 그때 그때마다 쿼리를 수정하기 어렵다고 판단되어 동적 쿼리를 고집하고 있는 상황입니다.
분명히 방법이 있을 것 같은데 도저히 모르겠어서 2주동안 구글링하고 찾아봐도 못찾겠어서 커뮤니티에 질문올립니다!!
1. CURSOR를 돌리면서 SELECT문을 따로 함께 사용할 수 있는 방법이 있을까요?
2. 아니면 PIVOT으로 해결할 수 있는 방법이 있을까요?
3. 그도 아니면 다른 방법이 있으시면 꼭좀 알려주시기를 부탁드리겠습니다 !!
by 마농
[2022.03.07 08:38:28]
WITH 학생테이블(학번, 학생이름, 입학일자, 졸업일자, 평균성적, 출석일, 담임이름) AS
(
SELECT 1, 'A', '2017-02-02', '2020-02-15', 99.5, 285, 'AB'
UNION ALL SELECT 2, 'B', '2015-02-02', '2018-02-12', 56.5, 260, 'BC'
UNION ALL SELECT 3, 'C', '2014-02-02', '2017-02-09', 98.7, 280, 'CD'
UNION ALL SELECT 4, 'D', '2019-02-02', '2022-02-22', 85.4, 267, 'DE'
)
, 대학테이블(학번, 입시항목, 입시방법, 원서접수, 원서비, 결과) AS
(
SELECT 1, '서울대', '정시', 'OK', 50000, '합격'
UNION ALL SELECT 1, '서울대', '수시', 'OK', 50000, '합격'
UNION ALL SELECT 2, '부경대', '수시', 'OK', 55000, '불합격'
UNION ALL SELECT 3, '서울대', '정시', 'OK', 55000, '합격'
UNION ALL SELECT 4, '고려대', '수시', 'OK', 55000, '합격'
UNION ALL SELECT 4, '고려대', '정시', NULL, NULL, NULL
)
SELECT 학번, 학생이름, 입학일자, 졸업일자, 평균성적, 출석일, 담임이름
, MIN(CASE rn WHEN 1 THEN 입시항목 END) 입시항목_1
, MIN(CASE rn WHEN 1 THEN 입시방법 END) 입시방법_1
, MIN(CASE rn WHEN 1 THEN 원서접수 END) 원서접수_1
, MIN(CASE rn WHEN 1 THEN 원서비 END) 원서비_1
, MIN(CASE rn WHEN 1 THEN 결과 END) 결과_1
, MIN(CASE rn WHEN 2 THEN 입시항목 END) 입시항목_2
, MIN(CASE rn WHEN 2 THEN 입시방법 END) 입시방법_2
, MIN(CASE rn WHEN 2 THEN 원서접수 END) 원서접수_2
, MIN(CASE rn WHEN 2 THEN 원서비 END) 원서비_2
, MIN(CASE rn WHEN 2 THEN 결과 END) 결과_2
, MIN(CASE rn WHEN 3 THEN 입시항목 END) 입시항목_3
, MIN(CASE rn WHEN 3 THEN 입시방법 END) 입시방법_3
, MIN(CASE rn WHEN 3 THEN 원서접수 END) 원서접수_3
, MIN(CASE rn WHEN 3 THEN 원서비 END) 원서비_3
, MIN(CASE rn WHEN 3 THEN 결과 END) 결과_3
FROM (SELECT a.학번
, a.학생이름, a.입학일자, a.졸업일자, a.평균성적, a.출석일, a.담임이름
, b.입시항목, b.입시방법, b.원서접수, b.원서비, b.결과
, ROW_NUMBER() OVER(PARTITION BY a.학번 ORDER BY b.입시항목, b.입시방법) rn
FROM 학생테이블 a
INNER JOIN 대학테이블 b
ON a.학번 = b.학번
-- WHERE 입학일자 >= '2015-01-01'
) a
GROUP BY 학번, 학생이름, 입학일자, 졸업일자, 평균성적, 출석일, 담임이름
;
by 축구쟁이
[2022.03.07 17:55:22]
방법이 여러가지 있습니다만, 데이터를 표시하는 방법에 대한 부분은 클라이언트 영역에서 작업이 이루어지는 것이 좋습니다.
문의하신 피벗을 활용한 방법이라면 아래 예시를 생각하신 것으로 예상하여 작성해보았습니다.
피벗 IN구문 안에 들어가는 텍스트를 동적 쿼리를 이용해 학번에 해당하는 입시 기준으로 만들어 작성하면 될 것 같습니다.
WITH 학생테이블(학번, 학생이름, 입학일자, 졸업일자, 평균성적, 출석일, 담임이름) AS
(
SELECT 1, 'A', '2017-02-02', '2020-02-15', 99.5, 285, 'AB'
UNION ALL SELECT 2, 'B', '2015-02-02', '2018-02-12', 56.5, 260, 'BC'
UNION ALL SELECT 3, 'C', '2014-02-02', '2017-02-09', 98.7, 280, 'CD'
UNION ALL SELECT 4, 'D', '2019-02-02', '2022-02-22', 85.4, 267, 'DE'
)
, 대학테이블(학번, 입시항목, 입시방법, 원서접수, 원서비, 결과) AS
(
SELECT 1, '서울대', '정시', 'OK', 50000, '합격'
UNION ALL SELECT 1, '서울대', '수시', 'OK', 50000, '합격'
UNION ALL SELECT 2, '부경대', '수시', 'OK', 55000, '불합격'
UNION ALL SELECT 3, '서울대', '정시', 'OK', 55000, '합격'
UNION ALL SELECT 4, '고려대', '수시', 'OK', 55000, '합격'
UNION ALL SELECT 4, '고려대', '정시', NULL, NULL, NULL
)
SELECT *
FROM (SELECT a.학생이름, a.졸업일자, a.평균성적, a.출석일, a.담임이름
, 입시항목 + '/' + 입시방법 AS 구분
, 입시방법 + '/' + 원서접수 + '/' + cast(원서비 as varchar(100)) + '/' + 결과 AS 결과
FROM 학생테이블 a
JOIN 대학테이블 b ON a.학번 = b.학번
) a
PIVOT (MIN(결과) FOR 구분 IN([서울대/정시], [서울대/수시], [부경대/수시], [고려대/정시], [고려대/수시])) as pt
) 버튼을 클릭하여 작성 하시면 됩니다.