PIVOT

MS-SQL

PIVOT

캡틴노랑이 2015. 7. 27. 13:33

NATION	MAN	WAMEN	X	TOTAL
korea	0	6	3	9
japna	1	2	3	6

http://msdn.microsoft.com/ko-kr/library/ms177410(v=sql.105).aspx

-----------------------------------------------------------------

SELECT 
        A.PjtCode
    ,   A.EstNo
    ,   A.PjtName
    ,   B.GrossArea                 --연면적
    ,   CONVERT(DECIMAL(12, 1), ROUND(B.GrossArea * 0.3025, 1, 1)) AS GrossAreaPY           --연면적(평)
    ,   B.BuildingArea              --건축면적
    ,   CONVERT(DECIMAL(12, 1), ROUND(B.BuildingArea * 0.3025, 1, -1)) AS BuildingAreaPY        --건축면적(평)
    ,   B.LandArea                  --대지면적  
    ,   CONVERT(DECIMAL(12, 1), ROUND(B.LandArea * 0.3025, 1, -1)) AS LandAreaPY            --대지면적  (평)
    ,   B.FloorAreaRate             --용적률
    ,   B.GrndFloors                --규모 지하
    ,   B.UndGrndFloors             --규모 지상
    ,   [01]--공법 1
    ,   [02]--공법 2
    ,   [03]--구조 1
    ,   [04]--구조 2
    ,   [05]--기타사항
    ,   [06]--기타사항
    ,   [07]--주요용도
    FROM dbo.DDETB_AA_EstRequest A WITH(NOLOCK)  --견적의뢰
    JOIN dbo.DDETB_AA_EstInitInfo B ON A.PjtCode = B.PjtCode AND A.EstNo =B.EstNo
    LEFT OUTER JOIN (
        SELECT
            PjtCode
        ,   EStNo
        ,   [01]--외장재
        ,   [02]--토목
        ,   [03]--구조
        ,   [04]--주요설비
        ,   [05]--기타1
        ,   [06]--기타2
        ,   [07]--주요용도
        FROM(
            SELECT 
                PjtCode
            ,   EStNo
            ,   WorkMathodDvs   --공통코드 DE41
            ,   WorkMethodNote  
            FROM dbo.DDETB_AA_WorkMethod 
            WHERE PjtCode =@PjtCode
            AND EstNo = @EstNo
        ) A
        PIVOT(
            MAX(WorkMethodNote) FOR WorkMathodDvs IN ([01], [02], [03], [04], [05], [06], [07])
        ) P 
    ) C ON A.PjtCode = C.PjtCode AND A.EstNo = C.EstNo  
    WHERE A.PjtCode =@PjtCode
    AND A.EstNo = @EstNo

저작자표시 (새창열림)