[MSSQL] 특정 배수만큼 ROW생성 SELECT 쿼리
[MSSQL] 특정 배수만큼 ROW생성 SELECT 쿼리
5의 배수만큼 ROW를 SELECT하는 쿼리
WITH TEMP_A AS
(
SELECT '1반' AS [반정보], '일월~' AS [이름], 1 AS [번호]
UNION ALL
SELECT '1반' AS [반정보], 'Feb!' AS [이름], 2 AS [번호]
UNION ALL
SELECT '1반' AS [반정보], '3월@' AS [이름], 3 AS [번호]
UNION ALL
SELECT '1반' AS [반정보], '사월#' AS [이름], 4 AS [번호]
UNION ALL
SELECT '2반' AS [반정보], 'May$' AS [이름], 5 AS [번호]
UNION ALL
SELECT '2반' AS [반정보], '6월%' AS [이름], 6 AS [번호]
UNION ALL
SELECT '2반' AS [반정보], '칠월~' AS [이름], 7 AS [번호]
UNION ALL
SELECT '2반' AS [반정보], 'Aug!' AS [이름], 8 AS [번호]
UNION ALL
SELECT '3반' AS [반정보], '9월@' AS [이름], 9 AS [번호]
UNION ALL
SELECT '3반' AS [반정보], '십월*' AS [이름], 10 AS [번호]
UNION ALL
SELECT '3반' AS [반정보], 'Nov&' AS [이름], 11 AS [번호]
UNION ALL
SELECT '3반' AS [반정보], '12월^' AS [이름], 12 AS [번호]
)
, TEMP_B AS
(
SELECT ROW_NUM AS ROW_NUM
, CASE WHEN ROW_NUM = CNT
THEN MAX_ROW
ELSE ROW_NUM
END AS MAX_ROW
, [반정보] AS [반정보]
, [이름] AS [이름]
FROM (
SELECT ROW_NUMBER() OVER(ORDER BY [번호]) AS ROW_NUM
, COUNT(*) OVER(PARTITION BY NULL) AS CNT
, (CEILING((COUNT(*)OVER(PARTITION BY NULL))/5.0)*5) AS MAX_ROW -- 5의 배수로 진행
, [반정보] AS [반정보]
, [이름] AS [이름]
FROM TEMP_A
) TEMP_BA
UNION ALL
SELECT ROW_NUM+1 AS ROW_NUM
, MAX_ROW AS MAX_ROW
, NULL AS [반정보]
, NULL AS [이름]
FROM TEMP_B
WHERE 1=1
AND ROW_NUM+1 <= MAX_ROW
)
SELECT *
FROM TEMP_B
