兑现行列转换

2012-12-24

实现行列转换现有代码if object_id(xmlTest) is not nullDROP TABLE xmlTestcreate table xmltest(id in

实现行列转换
现有代码


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?
[最优解释]

--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 行受影响)
*/

[其他解释]
MAX(CASE WHEN ..
[其他解释]


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
[其他解释]
不好意思,刚刚想到了
[其他解释]
动态的话搞不出顺序，除非用静态来写死月份。或者有一个辅助的，apr=4，aug=8月这样。
[其他解释]
我的回答记录里有好几条行转列的，楼主可以参考。
[其他解释]
可以吧行转换成列啊
[其他解释]
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]')

热点排行

SQL Server

兑现行列转换