맨밑에 계산값을 만들려고 하는데.. 12이하버전 이여서 lag 함수가 되질 않네요 ㅠ
이럴경우 어떻게 해야할까요?
WITH t AS
(
SELECT '길이' chitemseq, '19.7' chitem1, '19.8' chitem2, '20.2' chitem3
UNION ALL SELECT '폭', '15.0', '15.0', '15.2'
UNION ALL SELECT '무게', '3.82', '3.89', '4.04'
)
SELECT chitemseq
, chitem1, chitem2, chitem3
from t
;
계산은 무게/길이/폭*10000 입니다.
길이 | 19.7 | 19.8 | 20.2 |
폭 | 15 | 15 | 15.2 |
무게 | 3.82 | 3.89 | 4.04 |
계산 | 129.27 | 130.98 |
131.58 |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | -- MS-SQL 이시면 적당히 바꿔주세요. WITH t AS ( SELECT '길이' chitemseq, '19.7' chitem1, '19.8' chitem2, '20.2' chitem3 FROM DUAL UNION ALL SELECT '폭' , '15.0' , '15.0' , '15.2' FROM DUAL UNION ALL SELECT '무게' , '3.82' , '3.89' , '4.04' FROM DUAL ) SELECT chitemseq , chitem1, chitem2, chitem3 FROM t UNION ALL SELECT '계산' , TO_CHAR( MIN ( CASE WHEN chitemseq = '무게' THEN chitem1 END ) / MIN ( CASE WHEN chitemseq = '길이' THEN chitem1 END ) / MIN ( CASE WHEN chitemseq = '폭' THEN chitem1 END ) * 10000 ) AS chitem1 , TO_CHAR( MIN ( CASE WHEN chitemseq = '무게' THEN chitem2 END ) / MIN ( CASE WHEN chitemseq = '길이' THEN chitem2 END ) / MIN ( CASE WHEN chitemseq = '폭' THEN chitem2 END ) * 10000 ) AS chitem2 , TO_CHAR( MIN ( CASE WHEN chitemseq = '무게' THEN chitem3 END ) / MIN ( CASE WHEN chitemseq = '길이' THEN chitem3 END ) / MIN ( CASE WHEN chitemseq = '폭' THEN chitem3 END ) * 10000 ) AS chitem3 FROM t |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | WITH t AS ( SELECT '길이' chitemseq, '19.7' chitem1, '19.8' chitem2, '20.2' chitem3 UNION ALL SELECT '폭' , '15.0' , '15.0' , '15.2' UNION ALL SELECT '무게' , '3.82' , '3.89' , '4.04' ) SELECT * FROM ( SELECT gb , CAST (길이 AS VARCHAR ) 길이 , CAST (폭 AS VARCHAR ) 폭 , CAST (무게 AS VARCHAR ) 무게 , CAST (ROUND( CAST (무게 AS FLOAT ) / 길이 / 폭 * 10000, 2) AS VARCHAR ) 계산 FROM t UNPIVOT (x FOR gb IN (chitem1, chitem2, chitem3)) a PIVOT ( MIN (x) FOR chitemseq IN (길이, 폭, 무게)) a ) a UNPIVOT (x FOR chitemseq IN (길이, 폭, 무게, 계산)) a PIVOT ( MIN (x) FOR gb IN (chitem1, chitem2, chitem3)) a ORDER BY CHARINDEX(chitemseq, '길이, 폭, 무게, 계산' ) ; |