sql 理解
浏览数：22 /
时间：2015年06月12日
select b.*,
b.model_ent_name+cast(m.year as varchar)as modelname,
m.index_value as val
into #tb
from
(
select a.*,erm.model_ent_name
from
(select w.uuid,w.indexdef_id,
i.index_name, i.index_def,
case when w.years=2014 then w.actual_value else 0 end as k1,
case when w.years=2013 then w.actual_value else 0 end as k2,
case when w.years=2012 then w.actual_value else 0 end as k3,
case when w.years=2011 then w.actual_value else 0 end as k4,
case when w.years=2010 then w.actual_value else 0 end as k5
from bm_work w,bm_index i
where w.indexdef_id=i.uuid
and w.ent_id=‘2c90e4da49514c750149515eb56f0003‘ and w.summary_state=2
and years=2014
)a left join bm_ent_rel_me erm on a.uuid=erm.bmwork_id
)b
left join bm_model_ent m on b.indexdef_id=m.index_def_id and b.model_ent_name=m.model_ent_name
declare @sql varchar(8000)
select @sql=isnull(@sql+‘,‘,‘‘)+modelname from #tb group by modelname
set @sql = ‘select * from #tb pivot (sum(val) for modelname in (‘+@sql+‘) )a‘
exec (@sql)
b.model_ent_name+cast(m.year as varchar)as modelname,
m.index_value as val
into #tb
from
(
select a.*,erm.model_ent_name
from
(select w.uuid,w.indexdef_id,
i.index_name, i.index_def,
case when w.years=2014 then w.actual_value else 0 end as k1,
case when w.years=2013 then w.actual_value else 0 end as k2,
case when w.years=2012 then w.actual_value else 0 end as k3,
case when w.years=2011 then w.actual_value else 0 end as k4,
case when w.years=2010 then w.actual_value else 0 end as k5
from bm_work w,bm_index i
where w.indexdef_id=i.uuid
and w.ent_id=‘2c90e4da49514c750149515eb56f0003‘ and w.summary_state=2
and years=2014
)a left join bm_ent_rel_me erm on a.uuid=erm.bmwork_id
)b
left join bm_model_ent m on b.indexdef_id=m.index_def_id and b.model_ent_name=m.model_ent_name
declare @sql varchar(8000)
select @sql=isnull(@sql+‘,‘,‘‘)+modelname from #tb group by modelname
set @sql = ‘select * from #tb pivot (sum(val) for modelname in (‘+@sql+‘) )a‘
exec (@sql)
drop table #tb
需要将上图变为下图结果。
实现行转列，及把第一张图片中红圈的部分的内容放置到 列上，作为列的标题。。。
declare @sql varchar(8000)
select @sql=isnull(@sql+‘,‘,‘‘)+modelname from #tb group by modelname
set @sql = ‘select * from #tb pivot (sum(val) for modelname in (‘+@sql+‘) )a‘ //val 为值 modelname为需要将行转列
exec (@sql)
//写这种sql的思路就是，先自己写出类似图一 的结果，确定那一个需要行转列。。这里我们就是需要modelname 这一列的值，都放过去作为列的属性名。。。看图两个圈的描述吧。具体本人也不是很好描述。
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
