SQLSERVER行转列

sqlserver与mysql、oracle不同，sqlserver行转列需要自定义函数，就很恶心，感觉每次使用sqlserver都需要重新学下sql，哈哈哈…好了，话不多说，上代码！

一、自定义创建函数

CREATE FUNCTION splitl (
     @String VARCHAR ( MAX ),
     @Delimiter VARCHAR ( MAX )
) RETURNS @temptable TABLE (datepart VARCHAR ( MAX )) AS
BEGIN
     DECLARE @idx INT =1
     DECLARE @slice VARCHAR ( MAX )
     IF LEN(@String) < 1 OR LEN( ISNULL (@String, '' )) = 0
         RETURN
     WHILE @idx != 0
     BEGIN
         SET @idx = CHARINDEX(@Delimiter,@String)
         IF @idx != 0
             SET @slice = LEFT (@String,@idx - 1)
         ELSE
             SET @slice = @String
         IF LEN(@slice) > 0
             INSERT INTO @temptable(datepart) VALUES (@slice)
         SET @String = RIGHT (@String, LEN(@String) - @idx)
         IF LEN(@String) = 0
             BREAK
     END
     RETURN
END

二、自定义函数创建好了，就是引用函数：

select datepart, ISNULL(tocalAccessNum,0)  from (
SELECT  *  FROM  dbo.splitl( '1,2,3,4,5,6,7,8,9,10,11,12' , ',' ) a
LEFT JOIN (
SELECT
        DATEPART( mm, CreateDate ) times,
        COUNT ( * ) tocalAccessNum
        FROM
        ZJ_SZLY.dbo.Busi_Menu_Log 
        GROUP BY
        DATEPART(mm, CreateDate )
    ) b ON a.datepart = b.times
		) c