IF OBJECT_ID (N'DBO.V_CODE') IS NOT NULL
    DROP VIEW DBO.V_CODE;
GO

CREATE VIEW V_CODE
AS
    SELECT  CASE WHEN T.SORT_ORD = 9999 THEN '' ELSE ISNULL(T.CODE, T.GRP_CD)       END AS CODE
    ,       CASE WHEN T.SORT_ORD = 9999 THEN '' ELSE T.KO_KR                        END AS KO_KR
    ,       CASE WHEN T.SORT_ORD = 9999 THEN '' ELSE T.EN_US                        END AS EN_US
    ,       CASE WHEN T.SORT_ORD = 9999 THEN '' ELSE T.ZH_CN                        END AS ZH_CN
    ,       CASE WHEN T.SORT_ORD = 9999 THEN '' ELSE T.ZH_CN                        END AS JA_JP
    ,       CASE WHEN T.SORT_ORD = 9999 THEN '' ELSE CAST(T.SORT_ORD AS VARCHAR)    END AS SORT_ORD
    ,       CASE WHEN T.SORT_ORD = 9999 THEN '' ELSE T.USE_YN                       END AS USE_YN
    ,       CASE WHEN T.SORT_ORD = 9999 THEN '' ELSE ISNULL(T.REM, '')              END AS REM
    ,       CASE 
                WHEN T.GRP_CD IS NOT NULL THEN GRP_CD 
                ELSE CAST(ROW_NUMBER() OVER (ORDER BY T.GRP_CD, T.SORT_ORD) AS VARCHAR(10))
            END                                                                         AS GRP_CD
    ,       CASE WHEN T.SORT_ORD = 9999 THEN '' ELSE T.TYP_NM                       END AS TYP_NM

    FROM   (SELECT  G.SYS_ID
            ,       G.GRP_CD
            ,       NULL                                                    AS CODE
            ,       G.GRP_CD_NM                                             AS KO_KR
            ,       G.GRP_CD_EN_NM                                          AS EN_US
            ,       NULL                                                    AS ZH_CN
            ,       NULL                                                    AS JA_JP
            ,       D.SORT_ORD                                              AS SORT_ORD
            ,       G.USE_YN
            ,       G.REM
            ,       G.STS
            ,       T.DTL_CD_NM                                             AS TYP_NM
            FROM ESACDGP G
            JOIN ESACDDL T  ON  T.SYS_ID    = G.SYS_ID
                            AND T.LANG_CD   = 'ko_KR'
                            AND T.GRP_CD    = 'C003'
                            AND T.DTL_CD    = G.CD_TYP
            JOIN   (SELECT -1   AS SORT_ORD 
                    UNION ALL 
                    SELECT 9999 AS SORT_ORD) D ON 1 = 1 

            UNION ALL
            
            SELECT  D.SYS_ID
            ,       D.GRP_CD
            ,       D.DTL_CD                                                AS CODE
            ,       MIN(CASE WHEN L.LANG_CD = 'ko_KR' THEN L.DTL_CD_NM END) AS KO_KR
            ,       MIN(CASE WHEN L.LANG_CD = 'en_US' THEN L.DTL_CD_NM END) AS EN_US
            ,       MIN(CASE WHEN L.LANG_CD = 'zh_CN' THEN L.DTL_CD_NM END) AS ZH_CN
            ,       MIN(CASE WHEN L.LANG_CD = 'ja_JP' THEN L.DTL_CD_NM END) AS JA_JP
            ,       MIN(ISNULL(D.SORT_ORD, 999))                            AS SORT_ORD
            ,       MIN(D.USE_YN)                                           AS USE_YN
            ,       MIN(D.REM)                                              AS REM
            ,       MIN(D.STS)                                              AS STS
            ,       ''                                                      AS TYP_NM
            FROM ESACDDT D
            LEFT OUTER JOIN ESACDDL L   ON  L.SYS_ID  = D.SYS_ID
                                        AND L.LANG_CD IN ('ko_KR', 'en_US', 'zh_CN', 'ja_JP')
                                        AND L.GRP_CD  = D.GRP_CD
                                        AND L.DTL_CD  = D.DTL_CD

            GROUP BY    D.SYS_ID
            ,           D.GRP_CD
            ,           D.DTL_CD
            ) T

    WHERE T.SYS_ID = '100'
    AND T.STS <> 'D';


    -- SQLSERVER는 뷰안에 ORDER BY절 사용못한다.
    -- ORDER BY T.GRP_CD, T.SORT_ORD;