데이터베이스 질문과 답변

오라클 pivot -> 포스트그레 pivot으로 변경방법 부탁드립니다 ㅠㅠ 0 2 2,155

by 신입입니당 [SQL Query] 오라클 피벗 신입개발자 postgresql [2021.05.25 17:59:13] 

 오라클 pivot 쿼리를 postgresql 쿼리로 변경하려는데 아무리 해도 안되서 글을 올리게 됐습니다 ...ㅠㅠ
크로스탭 이용하면 변경 가능하다는데 하루종일
invalid return type
  Detail: SQL rowid datatype does not match return rowid datatype. 이 오류만 뜨네여..ㅠㅠㅠ
퇴근하고싶습니다...ㅠㅠ

오라클 쿼리입니다.
WITH T1 AS (
SELECT
	FRFR_AVRG_DMG_TRHGH,
	FRFR_DMG_DMCLS_NMVL,
	FRTRE_WDVLM
FROM
	TB_FFAS_STEM_WDVLM_C
WHERE
	FRFR_DMG_RPRSN_KOFTR_CD = '01'
GROUP BY
	FRFR_DMG_DMCLS_NMVL,
	FRFR_AVRG_DMG_TRHGH,
	FRTRE_WDVLM
ORDER BY
	FRFR_DMG_DMCLS_NMVL ASC,
	FRFR_AVRG_DMG_TRHGH ASC)
SELECT
	*
FROM
	T1 PIVOT (MIN(FRTRE_WDVLM) FOR FRFR_AVRG_DMG_TRHGH IN (
	'5' AS "V5",
	'6' ,
	'7' ,
	'8' ,
	'9' AS "V9",
	'10' AS "V10",
	'11' AS "V11",
	'12' AS "V12",
	'13' AS "V13",
	'14' AS "V14",
	'15' AS "V15",
	'16' AS "V16",
	'17' AS "V17",
	'18' AS "V18",
	'19' AS "V19",
	'20' AS "V20",
	'21' AS "V21",
	'22' AS "V22",
	'23' AS "V23",
	'24' AS "V24",
	'25' AS "V25",
	'26' AS "V26",
	'27' AS "V27",
	'28' AS "V28",
	'29' AS "V29",
	'30' AS "V30",
	'31' AS "V31",
	'32' AS "V32",
	'33' AS "V33",
	'34' AS "V34",
	'35' AS "V35"))

위 쿼리 실행시 화면 입니다

by 뉴비디비 [2021.05.25 22:33:29] 
-- 원본 데이터타입과 PIVOT 처리하는 데이터타입이 일치해야 할꺼예요.
SELECT 
	*
FROM CROSSTAB(
  'SELECT pv_part::integer, pv_name::varchar, val1::float FROM (
		SELECT 1 as pv_part, ''name1'' as pv_name, 0.26279790 as val1 UNION ALL
		SELECT 1, ''name2'', 0.58751053 UNION ALL
		SELECT 1, ''name3'', 0.85391529 UNION ALL
		SELECT 2, ''name1'', 0.04760031 UNION ALL
		SELECT 2, ''name2'', 1.49985265 UNION ALL
		SELECT 2, ''name3'', 0.26315365 UNION ALL
		SELECT 3, ''name1'', 0.28757050 UNION ALL
		SELECT 3, ''name2'', 0.53152904 UNION ALL
		SELECT 3, ''name3'', 0.13061260
	) tbl'
) AS pv_result 
	(pv_part integer /* numeric 일때 오류*/ , p_name1 float, p_name2 float, p_name3 float);

 

by 마농 [2021.05.27 09:52:47] 
SELECT frfr_dmg_dmcls_nmvl
     , MIN(CASE frfr_avrg_dmg_trhgh WHEN  '5' THEN frtre_wdvlm END) v5
     , MIN(CASE frfr_avrg_dmg_trhgh WHEN  '6' THEN frtre_wdvlm END) v6
     , MIN(CASE frfr_avrg_dmg_trhgh WHEN  '7' THEN frtre_wdvlm END) v7
     , MIN(CASE frfr_avrg_dmg_trhgh WHEN  '8' THEN frtre_wdvlm END) v8
     , MIN(CASE frfr_avrg_dmg_trhgh WHEN  '9' THEN frtre_wdvlm END) v9
     , MIN(CASE frfr_avrg_dmg_trhgh WHEN '10' THEN frtre_wdvlm END) v10
     , MIN(CASE frfr_avrg_dmg_trhgh WHEN '11' THEN frtre_wdvlm END) v11
     , MIN(CASE frfr_avrg_dmg_trhgh WHEN '12' THEN frtre_wdvlm END) v12
     , MIN(CASE frfr_avrg_dmg_trhgh WHEN '13' THEN frtre_wdvlm END) v13
     , MIN(CASE frfr_avrg_dmg_trhgh WHEN '14' THEN frtre_wdvlm END) v14
     , MIN(CASE frfr_avrg_dmg_trhgh WHEN '15' THEN frtre_wdvlm END) v15
     , MIN(CASE frfr_avrg_dmg_trhgh WHEN '16' THEN frtre_wdvlm END) v16
     , MIN(CASE frfr_avrg_dmg_trhgh WHEN '17' THEN frtre_wdvlm END) v17
     , MIN(CASE frfr_avrg_dmg_trhgh WHEN '18' THEN frtre_wdvlm END) v18
     , MIN(CASE frfr_avrg_dmg_trhgh WHEN '19' THEN frtre_wdvlm END) v19
     , MIN(CASE frfr_avrg_dmg_trhgh WHEN '20' THEN frtre_wdvlm END) v20
     , MIN(CASE frfr_avrg_dmg_trhgh WHEN '21' THEN frtre_wdvlm END) v21
     , MIN(CASE frfr_avrg_dmg_trhgh WHEN '22' THEN frtre_wdvlm END) v22
     , MIN(CASE frfr_avrg_dmg_trhgh WHEN '23' THEN frtre_wdvlm END) v23
     , MIN(CASE frfr_avrg_dmg_trhgh WHEN '24' THEN frtre_wdvlm END) v24
     , MIN(CASE frfr_avrg_dmg_trhgh WHEN '25' THEN frtre_wdvlm END) v25
     , MIN(CASE frfr_avrg_dmg_trhgh WHEN '26' THEN frtre_wdvlm END) v26
     , MIN(CASE frfr_avrg_dmg_trhgh WHEN '27' THEN frtre_wdvlm END) v27
     , MIN(CASE frfr_avrg_dmg_trhgh WHEN '28' THEN frtre_wdvlm END) v28
     , MIN(CASE frfr_avrg_dmg_trhgh WHEN '29' THEN frtre_wdvlm END) v29
     , MIN(CASE frfr_avrg_dmg_trhgh WHEN '30' THEN frtre_wdvlm END) v30
     , MIN(CASE frfr_avrg_dmg_trhgh WHEN '31' THEN frtre_wdvlm END) v31
     , MIN(CASE frfr_avrg_dmg_trhgh WHEN '32' THEN frtre_wdvlm END) v32
     , MIN(CASE frfr_avrg_dmg_trhgh WHEN '33' THEN frtre_wdvlm END) v33
     , MIN(CASE frfr_avrg_dmg_trhgh WHEN '34' THEN frtre_wdvlm END) v34
     , MIN(CASE frfr_avrg_dmg_trhgh WHEN '35' THEN frtre_wdvlm END) v35
  FROM tb_ffas_stem_wdvlm_c
 WHERE frfr_dmg_rprsn_koftr_cd = '01'
 GROUP BY frfr_dmg_dmcls_nmvl
;

 

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입