实现行列转换
现有代码
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]')