PIVOT

SELECT NATION , [M] MAN , [W] WAMEN , [X] X , [M] + [W] + [X] TOTAL FROM ( SELECT NATION , GENDER_TYPE FROM DBO.Table1 ) P PIVOT ( COUNT(GENDER_TYPE) FOR GENDER_TYPE IN ([M], [W], [X]) ) PIVOTTABLE ORDER BY NATION

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