[MS-SQL] pivot, dynamic pivot #동적쿼리 #CONVERT() #LEFT() #LEN()

DECLARE @Prod varchar(2000)

declare @sql varchar(2000)

SET @Prod = ''

SELECT @Prod = @Prod + '[' + mm + '],'

FROM (SELECT Distinct convert(char(6),등록일시,112) as mm FROM 고객정보) A

 

SET @Prod = LEFT(@Prod, LEN(@Prod) - 1)

EXEC('

       SELECT

        *

       FROM(

         SELECT

               고객번호

               , CONVERT(char(6),등록일시,112) as 등록월

         FROM 고객정보

       ) AS SOURCETABLE

    PIVOT (

             count(고객번호) FOR 등록월IN (' + @Prod + ')

    ) AS PVT'

)

---

실행결과:

201112	201201
4	1