【转】SQL中PIVOT 行列转换

转自：http://m.blog.csdn.net/beyondqd/article/details/50853815

 

 CREATE TABLE SalesByQuarter
    (    year INT,    -- 年份
        quarter CHAR(2),  -- 季度
        amount MONEY  -- 总额
    )

 

SET NOCOUNT ON
    DECLARE @index INT
    DECLARE @q INT
    SET @index = 0
    DECLARE @year INT
    while (@index < 30)
    BEGIN
        SET @year = 2005 + (@index % 4)
        SET @q = (CAST((RAND() * 500) AS INT) % 4) + 1
        INSERT INTO SalesByQuarter VALUES (@year, 'Q' + CAST(@q AS CHAR(1)), RAND() * 10000.00)
        SET @index = @index + 1
    END

 

 

    SELECT year as 年份
        , sum (case when quarter = 'Q1' then amount else 0 end) 一季度
        , sum (case when quarter = 'Q2' then amount else 0 end) 二季度
        , sum (case when quarter = 'Q3' then amount else 0 end) 三季度
        , sum (case when quarter = 'Q4' then amount else 0 end) 四季度
    FROM SalesByQuarter GROUP BY year ORDER BY year DESC

 

 

SELECT year as 年份, Q1 as 一季度, Q2 as 二季度, Q3 as 三季度, Q4 as 四季度 FROM SalesByQuarter PIVOT (SUM (amount) FOR quarter IN (Q1, Q2, Q3, Q4) ) AS P ORDER BY YEAR DESC