[ MS-SQL ] 간단한 쿼리인데 3개의 Row에 대해 계산해서 한행 추가

 C# 에 심취해서 쿼리에 대한 내용을 블로깅 해두지 않았었는데...

오라클을 다루다 보니 작업처리 방법등은 기록해두는것이 괜찮을듯 하다.



아래 색 처리한 부분은

F 3609565874.83333
K 0.002083333
T 369980.502170417

요런 데이타에

 = SQRT( (  2 * F * T ) * K )  라는 결과값을 한행 더 추가해야 될 경우

위 세개의 데이타를 옆으로 펼치면..

         F_AVG, T_AVG, K_AVG,  F_BASE, T_BASE, K_BASE, F_UPPER, T_UPPER, K_UPPER

형태로 변환해서 계산식을 적용해주어야 한다.

이때 위 쿼리에 컬럼을 하나 추가하여 그룹바이로 통합시켜주면 1개의 쿼리로 바꿀수 있게 된다.

방법 1)

--USE [TestDB]
--GO
/****** 개체:  Table [dbo].[Plan_Results]    스크립트 날짜: 10/23/2010 19:50:45 ******/
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO
--SET ANSI_PADDING ON
--GO
--CREATE TABLE [dbo].[Plan_Results](
-- [Gigum_Code] [char](1) COLLATE Korean_Wansung_CI_AS NOT NULL,
-- [YYYYMM] [char](7) COLLATE Korean_Wansung_CI_AS NOT NULL,
-- [Year0_SumAmt] [numeric](18, 6) NULL,
-- [Year3_SumAmt] [numeric](18, 6) NULL,
-- [Year5_SumAmt] [numeric](18, 6) NULL,
-- CONSTRAINT [PK_Plan_Results] PRIMARY KEY CLUSTERED
--(
-- [Gigum_Code] ASC,
-- [YYYYMM] ASC
--)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
--) ON [PRIMARY]
--
--GO
--SET ANSI_PADDING OFF

--insert into Plan_Results values ( 1 , '2010-01',   881728083,-     21455788882,-   26296054501    )
--insert into Plan_Results values ( 1 , '2010-02',  1663456166,-  93932311442,-  67724751121 )
--insert into Plan_Results values ( 1 , '2010-03', 10535184250,-  39727311735,-  27162544453 )
--insert into Plan_Results values ( 1 , '2010-04', 10916912333,-  37168124598,-  24826804875 )
--insert into Plan_Results values ( 1 , '2010-05', 10798640416,- 332748211345,- 200422034557  )
--insert into Plan_Results values ( 1 , '2010-06', 12360368500,- 546349938762,- 336875250117 )
--insert into Plan_Results values ( 1 , '2010-07',  8342096583,- 526987602935,- 321423734623 )
--insert into Plan_Results values ( 1 , '2010-08',  3223824666,- 395509716668,- 245811417447 )
--insert into Plan_Results values ( 1 , '2010-09',  5355552750,- 281646305135,- 157115087823 )  
--insert into Plan_Results values ( 1 , '2010-10',-  562719166,- 233716812282,- 136073606419 ) 
--insert into Plan_Results values ( 1 , '2010-11',- 7580991083,- 193765514755,- 109664461049 )
--insert into Plan_Results values ( 1 , '2010-12',-12619263000,- 176147709331,-  95575440266 )

DECLARE @OP FLOAT
DECLARE @OP2 FLOAT

SET @OP = 2.50 /100 /12   -- 적용금리
SET @OP2 = 0.123 /100 /12  -- 운용수수료

 SELECT 'F' AS [HEADER] ,
   AVG( YEAR0_SUMAMT ) AS [AVG],
   SUM( CASE WHEN RIGHT(YYYYMM,2)= '01' THEN YEAR0_SUMAMT ELSE 0 END ) AS [BASE],
   MAX( YEAR0_SUMAMT ) AS [UPPER]
 FROM PLAN_RESULTS
 GROUP BY GIGUM_CODE
UNION 
 SELECT 'K', @OP, @OP, @OP
UNION
 SELECT 'T' AS [HEADER] ,
   AVG( YEAR0_SUMAMT ) * @OP2 AS [AVG],
   SUM( CASE WHEN RIGHT(YYYYMM,2)= '01' THEN YEAR0_SUMAMT ELSE 0 END ) * @OP2 AS [BASE],
   MAX( YEAR0_SUMAMT ) * @OP2 AS [UPPER]
 FROM PLAN_RESULTS
 GROUP BY GIGUM_CODE

UNION

 SELECT 'C', SQRT( ( 2* SUM(CASE WHEN HEADER = 'F' THEN [AVG] ELSE 0 END ) * -- AVG1,
   SUM(CASE WHEN HEADER = 'T' THEN [AVG] ELSE 0 END ) ) / -- AVG2,
   SUM(CASE WHEN HEADER = 'K' THEN [AVG] ELSE 0 END ) ),   -- AVG3,

      SQRT( ( 2 * SUM(CASE WHEN HEADER = 'F' THEN [BASE] ELSE 0 END ) * -- BASE1,
   SUM(CASE WHEN HEADER = 'T' THEN [BASE] ELSE 0 END ) )/ -- BASE2,
   SUM(CASE WHEN HEADER = 'K' THEN [BASE] ELSE 0 END ) ), --BASE3,

   SQRT( (2 * SUM(CASE WHEN HEADER = 'F' THEN [UPPER] ELSE 0 END ) *-- UPPER1,
   SUM(CASE WHEN HEADER = 'T' THEN [UPPER] ELSE 0 END ) )/ -- UPPER2,
   SUM(CASE WHEN HEADER = 'K' THEN [UPPER] ELSE 0 END ) ) -- UPPER3

 FROM
 (
  SELECT 0 [KEY], 'F' AS [HEADER] ,
    AVG( YEAR0_SUMAMT ) AS [AVG],
    SUM( CASE WHEN RIGHT(YYYYMM,2)= '01' THEN YEAR0_SUMAMT ELSE 0 END ) AS [BASE],
    MAX( YEAR0_SUMAMT ) AS [UPPER]
  FROM PLAN_RESULTS
  GROUP BY GIGUM_CODE
  UNION 
  SELECT 0, 'K', @OP, @OP, @OP
  UNION
  SELECT 0, 'T' AS [HEADER] ,
    AVG( YEAR0_SUMAMT ) * @OP2 AS [AVG],
    SUM( CASE WHEN RIGHT(YYYYMM,2)= '01' THEN YEAR0_SUMAMT ELSE 0 END ) * @OP2 AS [BASE],
    MAX( YEAR0_SUMAMT ) * @OP2 AS [UPPER]
  FROM PLAN_RESULTS
  GROUP BY GIGUM_CODE
 ) T
 GROUP BY [KEY]

UNION

 SELECT '_C', SQRT( ( 2* SUM(CASE WHEN HEADER = 'F' THEN [AVG] ELSE 0 END ) * -- AVG1,
   SUM(CASE WHEN HEADER = 'T' THEN [AVG] ELSE 0 END ) ) / -- AVG2,
   SUM(CASE WHEN HEADER = 'K' THEN [AVG] ELSE 0 END ) )    -- AVG3,
    + SUM( CASE WHEN HEADER = 'F' THEN [BASE] ELSE 0 END ),
   

      SQRT( ( 2 * SUM(CASE WHEN HEADER = 'F' THEN [BASE] ELSE 0 END ) * -- BASE1,
   SUM(CASE WHEN HEADER = 'T' THEN [BASE] ELSE 0 END ) )/ -- BASE2,
   SUM(CASE WHEN HEADER = 'K' THEN [BASE] ELSE 0 END ) ) --BASE3,
    + SUM( CASE WHEN HEADER = 'F' THEN [BASE] ELSE 0 END  ),

   SQRT( (2 * SUM(CASE WHEN HEADER = 'F' THEN [UPPER] ELSE 0 END ) *-- UPPER1,
   SUM(CASE WHEN HEADER = 'T' THEN [UPPER] ELSE 0 END ) )/ -- UPPER2,
   SUM(CASE WHEN HEADER = 'K' THEN [UPPER] ELSE 0 END ) ) -- UPPER3
    + SUM( CASE WHEN HEADER = 'F' THEN [BASE] ELSE 0 END )

 FROM
 (
  SELECT 0 [KEY], 'F' AS [HEADER] ,
    AVG( YEAR0_SUMAMT ) AS [AVG],
    SUM( CASE WHEN RIGHT(YYYYMM,2)= '01' THEN YEAR0_SUMAMT ELSE 0 END ) AS [BASE],
    MAX( YEAR0_SUMAMT ) AS [UPPER]
  FROM PLAN_RESULTS
  GROUP BY GIGUM_CODE
  UNION 
  SELECT 0, 'K', @OP, @OP, @OP
  UNION
  SELECT 0, 'T' AS [HEADER] ,
    AVG( YEAR0_SUMAMT ) * @OP2 AS [AVG],
    SUM( CASE WHEN RIGHT(YYYYMM,2)= '01' THEN YEAR0_SUMAMT ELSE 0 END ) * @OP2 AS [BASE],
    MAX( YEAR0_SUMAMT ) * @OP2 AS [UPPER]
  FROM PLAN_RESULTS
  GROUP BY GIGUM_CODE
 ) T
 GROUP BY [KEY]