SQL comprehension, SQL
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 partition (sum (val) for modelname in ('+ @ SQL +')'
Exec (@ SQL)
Drop table # tb
It must be changed to result.
Implement row-to-column conversion, and place the content in the Red Circle of the first image on the column as the column title...
Declare @ SQL varchar (8000)
Select @ SQL = isnull (@ SQL + ',', '') + modelname from # tb group by modelname
Set @ SQL = 'select * from # tb partition (sum (val) for modelname in ('+ @ SQL + ')) a' // val is the value modelname. You need to convert rows to columns.
Exec (@ SQL)
// The idea of writing this SQL statement is to first write the result similar to figure 1 and determine which one requires row-to-column conversion .. Here we need to put the values of the modelname column in the past as the attribute name of the column... See the two circles in the figure. I am not very familiar with the specific description.