|
WITH TBL AS (
SELECT 'AAA' AS test_cd , 'Y' AS grp_flg, 'Y' AS inc_flg
UNION ALL
SELECT 'BBB' AS test_cd , 'N' AS grp_flg, 'Y' AS inc_flg
UNION ALL
SELECT 'CCC' AS test_cd , 'N' AS grp_flg, 'Y' AS inc_flg
UNION ALL
SELECT 'DDD' AS test_cd , 'N' AS grp_flg, 'N' AS inc_flg
UNION ALL
SELECT 'EEE' AS test_cd , 'N' AS grp_flg, 'N' AS inc_flg
UNION ALL
SELECT 'FFF' AS test_cd , 'N' AS grp_flg, 'N' AS inc_flg
UNION ALL
SELECT 'ABC' AS test_cd , 'N' AS grp_flg, 'Y' AS inc_flg
UNION ALL
SELECT 'ADA' AS test_cd , 'N' AS grp_flg, 'N' AS inc_flg
UNION ALL
SELECT 'BCD' AS test_cd , 'N' AS grp_flg, 'N' AS inc_flg
UNION ALL
SELECT 'CDF' AS test_cd , 'N' AS grp_flg, 'N' AS inc_flg
UNION ALL
SELECT 'EFG' AS test_cd , 'N' AS grp_flg, 'N' AS inc_flg
),
RST AS (
SELECT STUFF((
SELECT ',' + b.test_cd
FROM TBL b
WHERE b.grp_flg = 'Y' AND b.inc_flg = 'Y'
FOR XML PATH('')),1,1,'') AS grp_cd
, STUFF((
SELECT ',' + b.test_cd
FROM TBL b
WHERE b.grp_flg = 'N' AND b.inc_flg = 'Y'
FOR XML PATH('') ),1,1,'') AS inc_cd
, STUFF((
SELECT ',' + b.test_cd
FROM TBL b
WHERE (b.grp_flg = 'Y'OR b.inc_flg = 'Y')
FOR XML PATH('')),1,1,'') AS grp_inc_list
, STUFF((
SELECT ',' + b.test_cd
FROM TBL b
WHERE b.grp_flg = 'N' AND b.inc_flg = 'N'
AND b.test_cd NOT IN ('AAA','BBB','CCC','DDD','EEE','FFF')
FOR XML PATH('') ),1,1,'') AS oth_cd_list
FROM TBL a )
SELECT R.*
FROM RST R
샘플 데이터 조회결과
grp_cd|inc_cd|grp_inc_list|oth_cd
AAA|BBB,CCC|AAA,BBB,CCC|ABC,ADA,BCD,CDF,EFG
AAA|BBB,CCC|AAA,BBB,CCC|ABC,ADA,BCD,CDF,EFG
AAA|BBB,CCC|AAA,BBB,CCC|ABC,ADA,BCD,CDF,EFG
AAA|BBB,CCC|AAA,BBB,CCC|ABC,ADA,BCD,CDF,EFG
AAA|BBB,CCC|AAA,BBB,CCC|ABC,ADA,BCD,CDF,EFG
AAA|BBB,CCC|AAA,BBB,CCC|ABC,ADA,BCD,CDF,EFG
AAA|BBB,CCC|AAA,BBB,CCC|ABC,ADA,BCD,CDF,EFG
AAA|BBB,CCC|AAA,BBB,CCC|ABC,ADA,BCD,CDF,EFG
AAA|BBB,CCC|AAA,BBB,CCC|ABC,ADA,BCD,CDF,EFG
AAA|BBB,CCC|AAA,BBB,CCC|ABC,ADA,BCD,CDF,EFG
AAA|BBB,CCC|AAA,BBB,CCC|ABC,ADA,BCD,CDF,EFG
1. grp_cd - grp_flg가 Y인 코드
2. inc_cd - inc_flg가 Y인 코드
3. grp_inc_list - grp_cd와 inc_cd값을 합쳐놓은 코드
4. oth_cd - 그외의 코드
코드 설명
1. grp_flg가 Y인값이 대표 코드 - 1개만 Y로 지정가능 grp_flg가 Y인코드는 inc_flg도 Y
2. inc_flg가 Y인값들은 grp_flg가 Y인 값과 함께 보여줄 포함코드 다수 지정 가능
문의내용
위 샘플 데이터 기준으로 아래와같이 출력을 하고싶습니다.
1. 'AAA','BBB','CCC','DDD','EEE','FFF' 중에 grp_flg가 Y이며 inc_flg가 Y가 있을 경우
컬럼 : AAA_CD|BBB_CD|CCC_CD|DDD_CD|EEE_CD|FFF_CD|OTH_CD
데이터 : AAA,BBB,CCC,ABC|||DDD|EEE|FFF|ABC,ADA,BCD,CDF,EFG
2. 'AAA','BBB','CCC','DDD','EEE','FFF' 중에 grp_flg가 Y지만 inc_flg가 N일 경우
컬럼 : AAA_CD|BBB_CD|CCC_CD|DDD_CD|EEE_CD|FFF_CD|OTH_CD
데이터 : AAA,ABC|BBB|CCC|DDD|EEE|FFF|ABC,ADA,BCD,CDF,EFG
3. 'AAA','BBB','CCC','DDD','EEE','FFF'중에 grp_flg가 Y인 코드가 없을경우
컬럼 : AAA_CD|BBB_CD|CCC_CD|DDD_CD|EEE_CD|FFF_CD|OTH_CD
데이터 : AAA|BBB|CCC|DDD|EEE|FFF|ABC,ABC,ADA,BCD,CDF,EFG
1, 2, 3의 경우의수로 동적으로 조합하도록 만들고싶습니다..
grp_flg와 inc_flg의 Y/N 여부에 따라 텍스트를 동적으로 변경시키고 싶습니다.
어떻게해야할지 감이안오네요
|
