数据库里面有一个表 记录如下name month moneyjack 1 1000
jack 2 2000
jack 3 3000
mike 1 4000
mike 2 5000
mike 3 6000现在想得到下面的结果 怎么转换name 一月 二月 三月
jack 1000 2000 3000
mike 4000 5000 6000这是一道面试题目,不用存储过程 ,只能用sql写
jack 2 2000
jack 3 3000
mike 1 4000
mike 2 5000
mike 3 6000现在想得到下面的结果 怎么转换name 一月 二月 三月
jack 1000 2000 3000
mike 4000 5000 6000这是一道面试题目,不用存储过程 ,只能用sql写
这个问题在论坛上蛮多的
declare @s varchar(8000)
set @s='select name'
select @s=@s+'['+month+']月,=sum(case when month '''+rtrim(month)+''' then month end)' from tablename group by month
exec(@s+' from tablename name')
晕啊,这种问题怎么这么多呢
SUM(CASE monTh WHEN 1 THEN money ELSE 0 END) AS '一月',
SUM(CASE monTh WHEN 2 THEN money ELSE 0 END) AS '二月',
SUM(CASE monTh WHEN 3 THEN money ELSE 0 END) AS '三月',
SUM(CASE monTh WHEN 4 THEN money ELSE 0 END) AS '四月'
FROM biaoming
GROUP BY name
SUM(CASE [month] WHEN 1 THEN [money] ELSE 0 END) AS 一月,
SUM(CASE [month] WHEN 2 THEN [money] ELSE 0 END) AS 二月,
SUM(CASE [month] WHEN 3 THEN [money] ELSE 0 END) AS 三月
from table1
group by [name]
sum(case when month = 2 then money else 0 end) as 二月,
sum(case when month = 3 then money else 0 end) as 三月
from table
group by name