首页 > 代码库 > 一道sql 关于pivot的面试题

一道sql 关于pivot的面试题

技术分享

分析:其实它是对时间月份行转列的

表结构设计:

技术分享

结果:

select Name,moth,ISNULL([01],0) as Value1,ISNULL([02],0) as Value2,ISNULL([03],0) as Value3 from (
select a.Name,CONVERT(varchar(4),moth,120) as moth,CONVERT(varchar(2),moth,100) as moth2,number from Table_Type as a
left join Table_SHUJU as b on a.id=b.id
) as table1 pivot (sum(number) for moth2 in([01],[02],[03])) as table2

 

效果图:

技术分享

 

一道sql 关于pivot的面试题