行转列

pmandy

于 2014-07-22 11:30:24 发布

阅读量161

点赞数

CC 4.0 BY-SA版权

本文链接：https://blog.youkuaiyun.com/pmandy/article/details/84617331

SELECT PlayMonth ,
        ISNULL(中国人保车险, 0) AS 中国人保车险 ,
        ISNULL(中国平安车险, 0) AS 中国平安车险 ,
        ISNULL(太平洋保险车险, 0) AS 太平洋保险车险
FROM    ( SELECT    o.SeriesName ,
                    PlayMonth ,
                    CONVERT(DECIMAL(19, 1), SUM(playtime)) playtime
          FROM      dbo.r_ads AS a
                    INNER JOIN dbo.ProductSeries AS o ON a.seriesID = o.SeriesID
          WHERE     a.seriesID IN ( 4369, 4366, 4381 )
                    AND PlayDay BETWEEN '20130601' AND '20140601 '
                    AND ChId IN ( 34, 113, 136, 148, 154, 166, 167, 180, 234,
                                  294, 393, 474, 599, 682, 722, 724, 730, 774 )
          GROUP BY o.SeriesName ,
                    PlayMonth
        ) z PIVOT( MAX(playtime) FOR SeriesName IN ( [中国人保车险], [中国平安车险],
                                                     [太平洋保险车险] ) ) x