实现行列转换

Question

现有代码if object_id('xmlTest') is not null
DROP TABLE xmlTest
create table xmltest(
id int identity(1,1),
iyear varchar(20),
imonth varchar(20),
iamount varchar(20)
)
insert into xmlTest (iyear,imonth,iamount)
select '2010','Jan','21900' union all
select '2010','Feb','19700' union all
select '2010','Mar','11800' union all
select '2010','Apr','11000' union all
select '2010','May','15000' union all
select '2010','Jun','11800' union all
select '2010','Jul','9800'  union all
select '2010','Aug','21700' union all
select '2010','Sep','11700' union all
select '2010','Oct','11900' union all
select '2010','Nov','0'     union all
select '2010','Dec','0'     union all
select '2011','Jan','27400' union all
select '2011','Feb','29800' union all
select '2011','Mar','25800' union all
select '2011','Apr','26800' union all
select '2011','May','29600' union all
select '2011','Jun','32600' union all
select '2011','Jul','31800' union all
select '2011','Aug','36700' union all
select '2011','Sep','29700' union all
select '2011','Oct','31900' union all
select '2011','Nov','34800' union all
select '2011','Dec','24800' union all
select '2012','Jan','10000' union all
select '2012','Feb','11500' union all
select '2012','Mar','12500' union all
select '2012','Apr','15000' union all
select '2012','May','11000' union all
select '2012','Jun','9800'  union all
select '2012','Jul','11800' union all
select '2012','Aug','19700' union all
select '2012','Sep','21700' union all
select '2012','Oct','21900' union all
select '2012','Nov','22900' union all
select '2012','Dec','20800'想得到
      JAN  FEB MAR APR MAY JUN JUL AU SEP OCT NOV DEC
2010
2011
2012这样的结果应该怎么写SQL?

Answer 1

select iYear as Year ,
  max(case iMonth when 'JAN' then iAmount else 0 end) JAN,
  max(case iMonth when 'FEB' then iAmount else 0 end) FEB,
  max(case iMonth when 'MAR' then iAmount else 0 end) MAR,
  max(case iMonth when 'APR' then iAmount else 0 end) APR,
  max(case iMonth when 'MAY' then iAmount else 0 end) MAY,
  max(case iMonth when 'JUN' then iAmount else 0 end) JUN,
  max(case iMonth when 'JUL' then iAmount else 0 end) JUL,
  max(case iMonth when 'Aug' then iAmount else 0 end) Aug,
  max(case iMonth when 'SEP' then iAmount else 0 end) SEP,
  max(case iMonth when 'OCT' then iAmount else 0 end) OCT,
  max(case iMonth when 'NOV' then iAmount else 0 end) NOV,
  max(case iMonth when 'DEC' then iAmount else 0 end) DEC
from xmlTest
group by iYear
结果
Year   JAN    FEB    MAR    APR    MAY   JUN    JUL     Aug    SEP   OCT     NOV   DEC
2010  21900  19700  11800  11000  15000  11800  9800   21700  11700  11900   0      0
2011  27400  29800  25800  26800  29600  32600  31800  36700  29700  31900  34800  24800
2012  10000  11500  12500  15000  11000  9800   11800  19700  21700  21900  22900  20800

Answer 2

--IF OBJECT_ID('xmlTest') IS NOT NULL 
--    DROP TABLE xmlTest
--CREATE TABLE xmltest
--    (
--      id INT IDENTITY(1, 1) ,
--      iyear VARCHAR(20) ,
--      imonth VARCHAR(20) ,
--      iamount VARCHAR(20)
--    )
--INSERT  INTO xmlTest
--        ( iyear ,
--          imonth ,
--          iamount
--        )
--        SELECT  '2010' ,
--                'Jan' ,
--                '21900'
--        UNION ALL
--        SELECT  '2010' ,
--                'Feb' ,
--                '19700'
--        UNION ALL
--        SELECT  '2010' ,
--                'Mar' ,
--                '11800'
--        UNION ALL
--        SELECT  '2010' ,
--                'Apr' ,
--                '11000'
--        UNION ALL
--        SELECT  '2010' ,
--                'May' ,
--                '15000'
--        UNION ALL
--        SELECT  '2010' ,
--                'Jun' ,
--                '11800'
--        UNION ALL
--        SELECT  '2010' ,
--                'Jul' ,
--                '9800'
--        UNION ALL
--        SELECT  '2010' ,
--                'Aug' ,
--                '21700'
--        UNION ALL
--        SELECT  '2010' ,
--                'Sep' ,
--                '11700'
--        UNION ALL
--        SELECT  '2010' ,
--                'Oct' ,
--                '11900'
--        UNION ALL
--        SELECT  '2010' ,
--                'Nov' ,
--                '0'
--        UNION ALL
--        SELECT  '2010' ,
--                'Dec' ,
--                '0'
--        UNION ALL
--        SELECT  '2011' ,
--                'Jan' ,
--                '27400'
--        UNION ALL
--        SELECT  '2011' ,
--                'Feb' ,
--                '29800'
--        UNION ALL
--        SELECT  '2011' ,
--                'Mar' ,
--                '25800'
--        UNION ALL
--        SELECT  '2011' ,
--                'Apr' ,
--                '26800'
--        UNION ALL
--        SELECT  '2011' ,
--                'May' ,
--                '29600'
--        UNION ALL
--        SELECT  '2011' ,
--                'Jun' ,
--                '32600'
--        UNION ALL
--        SELECT  '2011' ,
--                'Jul' ,
--                '31800'
--        UNION ALL
--        SELECT  '2011' ,
--                'Aug' ,
--                '36700'
--        UNION ALL
--        SELECT  '2011' ,
--                'Sep' ,
--                '29700'
--        UNION ALL
--        SELECT  '2011' ,
--                'Oct' ,
--                '31900'
--        UNION ALL
--        SELECT  '2011' ,
--                'Nov' ,
--                '34800'
--        UNION ALL
--        SELECT  '2011' ,
--                'Dec' ,
--                '24800'
--        UNION ALL
--        SELECT  '2012' ,
--                'Jan' ,
--                '10000'
--        UNION ALL
--        SELECT  '2012' ,
--                'Feb' ,
--                '11500'
--        UNION ALL
--        SELECT  '2012' ,
--                'Mar' ,
--                '12500'
--        UNION ALL
--        SELECT  '2012' ,
--                'Apr' ,
--                '15000'
--        UNION ALL
--        SELECT  '2012' ,
--                'May' ,
--                '11000'
--        UNION ALL
--        SELECT  '2012' ,
--                'Jun' ,
--                '9800'
--        UNION ALL
--        SELECT  '2012' ,
--                'Jul' ,
--                '11800'
--        UNION ALL
--        SELECT  '2012' ,
--                'Aug' ,
--                '19700'
--        UNION ALL
--        SELECT  '2012' ,
--                'Sep' ,
--                '21700'
--        UNION ALL
--        SELECT  '2012' ,
--                'Oct' ,
--                '21900'
--        UNION ALL
--        SELECT  '2012' ,
--                'Nov' ,
--                '22900'
--        UNION ALL
--        SELECT  '2012' ,
--                'Dec' ,
--                '20800' 
DECLARE @s NVARCHAR(4000)
SET @s = ''
SELECT  @s = @s + ',' + QUOTENAME(imonth) + '=max(case when [imonth]='
        + QUOTENAME(imonth, '''') + ' then [iamount] else 0 end)'
FROM    xmlTest
GROUP BY imonth
--PRINT @s
EXEC('select [iyear]'+@s+' from xmlTest group by [iyear]') 
/*
iyear                Apr         Aug         Dec         Feb         Jan         Jul         Jun         Mar         May         Nov         Oct         Sep
-------------------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
2010                 11000       21700       0           19700       21900       9800        11800       11800       15000       0           11900       11700
2011                 26800       36700       24800       29800       27400       31800       32600       25800       29600       34800       31900       29700
2012                 15000       19700       20800       11500       10000       11800       9800        12500       11000       22900       21900       21700(3 行受影响)
*/

Answer 3

动态的话搞不出顺序，除非用静态来写死月份。或者有一个辅助的，apr=4，aug=8月这样。

Answer 4

declare @s nvarchar(4000)set @s=''  Select    
@s=@s+','+quotename([imonth])+'=max(case when [imonth]='+quotename([imonth],'''')+
' then [iamount] else 0 end)'
from xmltest group by[imonth] exec('select [iyear]'+@s+' from xmltest group by [iyear]')