| ID | GUBUN | RATE | DESC | REG_DATE |
| 김태희 | AGE | 39 | 2020-09-06 | |
| 김태희 | GENDER | F | 2020-09-06 | |
| 김태희 | AGEGROUP | OVER45 | 2020-09-06 | |
| 김태희 | SPORTS | 0.053 | 2020-09-06 | |
| 김태희 | SHOPPING | 0.044 | 2020-09-06 | |
| 김태희 | MUSIC | 0.33 | 2020-09-06 | |
| 비 | AGE | 38 | 2020-09-06 | |
| 비 | GENDER | M | 2020-09-06 | |
| 비 | AGEGROUP | OVER40 | 2020-09-06 | |
| 비 | BUSINESS | 0.88 | 2020-09-06 | |
| 비 | FINANCE | 0.77 | 2020-09-06 | |
| 비 | MUSIC | 0.98 | 2020-09-06 |
위와 같은 내용으로 INTEREST 라는 테이블에 내용이 들어가있습니다
위 내용을 아래와 같은 형태로 프로시져를 이용해서 TEST1 테이블에 넣고 싶어요
CURSOR 를 이용해서 한나씩 돌려서 넣어야 할거 같은데
변수를 어떻게 설정해야 할지
| ID | GENDER | AGE | AGEGROUP | MUSIC | SPORTS | REG_DATE |
| 김태희 | F | 39 | OVER45 | 0.33 | 0.053 | 2020-09-06 |
| 비 | M | 38 | OVER40 | 0.98 | 0.77 | 2020-09-06 |
프로시져 형태를 어떻게 잡아야 하는지 도움 부탁 드립니다
그리 어려운 내용은 아닐거 같기도 한데 제가 초보라 ....
파일로 테이블 내용 첨부했습니다
감사합니다
Comment 2
-
지영아빠
2020.09.07 10:03
with tmp as (select '김태희' as ID, 'AGE' as Gubun, '' as Rate, '39' as [Desc], '2020-09-06' as REG_DATE union allselect '김태희' as ID, 'GENDER' as Gubun, '' as Rate, 'F' as [Desc], '2020-09-06' as REG_DATE union allselect '김태희' as ID, 'AGEGROUP' as Gubun, '' as Rate, 'OVER45' as [Desc], '2020-09-06' as REG_DATE union allselect '김태희' as ID, 'SPORTS' as Gubun, '0.053' as Rate, '' as [Desc], '2020-09-06' as REG_DATE union allselect '김태희' as ID, 'SHOPPING' as Gubun, '0.044' as Rate, '' as [Desc], '2020-09-06' as REG_DATE union allselect '김태희' as ID, 'MUSIC' as Gubun, '0.33' as Rate, '' as [Desc], '2020-09-06' as REG_DATE union allselect '비' as ID, 'AGE' as Gubun, '' as Rate, '38' as [Desc], '2020-09-06' as REG_DATE union allselect '비' as ID, 'GENDER' as Gubun, '' as Rate, 'M' as [Desc], '2020-09-06' as REG_DATE union allselect '비' as ID, 'AGEGROUP' as Gubun, '' as Rate, 'OVER40' as [Desc], '2020-09-06' as REG_DATE union allselect '비' as ID, 'BUSINESS' as Gubun, '0.88' as Rate, '' as [Desc], '2020-09-06' as REG_DATE union allselect '비' as ID, 'FINANCE' as Gubun, '0.77' as Rate, '' as [Desc], '2020-09-06' as REG_DATE union allselect '비' as ID, 'MUSIC' as Gubun, '0.98' as Rate, '' as [Desc], '2020-09-06' as REG_DATE)select *from (selectID, GUBUN, max(RATE + [DESC]) as VALfrom tmpgroup by ID, GUBUN) as apivot (max(val) for Gubun in (AGE, GENDER, AGEGROUP, SPORTS, SHOPPING, MUSIC, BUSINESS, FINANCE)) as P -
아침고기
2020.09.07 15:27
딱 제가 원하는 형태네요 감사합니다
명쾌한 답변 잘 봤습니다
max(rate+[desc]) 여기가 행당하는 칼럼에 값이 있으면 구분자와 값을 올려주는 부분인가보군요
감사합니다
