SQL구문에서 여러줄결과값을 한줄로 보여주기
함수... 함수를 사용하면됨
그러나 함수사용 제약이 있을경우
ComCd ComNm ComSubCd ComSubNm
----- -------------------- -------- --------------------
A001 모니터 17 17인치모니터
A001 모니터 19 19인치모니터
A001 모니터 22 22인치모니터
A002 책상 22 22형책상
A002 책상 27 27형책상
항목을 구지 아래와 같이 보여야 되는 사항이라면
ComSubCd ComNm
-------- ----------------------------------------------------
17 17인치모니터
19 19인치모니터
22 모니터-22인치모니터; 책상-22형책상;
27 27형책상
(4개 행이 영향을 받음)
FOR XML PATH('')) 사용하심~~.
--- 예제
DECLARE @D_Code VARCHAR(50)
SELECT @D_Code = '';
SELECT ComCd, ComNm, ComSubCd, ComSubNm
FROM ComCdMain
WHERE CONVERT(VARCHAR, GETDATE(),112) BETWEEN FrYmd AND ToYmd
AND ( ComCd LIKE @D_Code+'%' OR ComNm LIKE @D_Code+'%')
GROUP BY ComCd, ComNm,ComSubCd, ComSubNm
;
--유형서브코드
WITH #T_INSU2 AS
(
SELECT *
FROM ComCdMain
WHERE CONVERT(VARCHAR, GETDATE(),112) BETWEEN FrYmd AND ToYmd
)
, #T_INCD AS
(
SELECT ComCd, ComNm
FROM #T_INSU2
GROUP BY ComCd, ComNm
)
, #T_SUBCD AS
(
SELECT ComCd, ComNm, ComSubCd, ComSubNm
FROM #T_INSU2
GROUP BY ComCd, ComNm, ComSubCd, ComSubNm
)
, #T_SUBDIST AS
(
SELECT ComSubCd, ComSubNm, COUNT(1) AS SubCnt
FROM #T_SUBCD
GROUP BY ComSubCd, ComSubNm
)
, #T_SUBCNT AS
(
SELECT ComSubCd, COUNT(1) AS SubCnt
FROM #T_SUBDIST
GROUP BY ComSubCd
)
SELECT
SB.ComSubCd
, CASE SB.SubCnt
WHEN '0' THEN ''
WHEN '1' THEN ( SELECT TOP 1 ComSubNm FROM #T_INSU2 WHERE ComSubCd = SB.ComSubCd )
ELSE (
SELECT ComNm +'-'+ ComSubNm +'; ' FROM #T_INSU2 WHERE ComSubCd = SB.ComSubCd FOR XML PATH(''))
END AS 'ComNm'
FROM #T_SUBCNT AS SB
WHERE ComSubCd LIKE @D_Code+'%'
OR CASE SB.SubCnt
WHEN '0' THEN ''
WHEN '1' THEN ( SELECT TOP 1 ComSubNm FROM #T_INSU2 WHERE ComSubCd = SB.ComSubCd )
ELSE (
SELECT ComNm +'-'+ ComSubNm +'; ' FROM #T_INSU2 WHERE ComSubCd = SB.ComSubCd FOR XML PATH(''))
END LIKE '%'+@D_Code+'%'
;
