行转列

先建一张临时表,把要赋值的数据和id插入到临时表中,再把要修改的表进行修改,

删除临时表

例如:在论文中新加一个字段,要显示所有的通讯作者,把所有的通讯作者和论文的id放在临时表中的id和name字段中,把论文的id进行关联,

在进行修改论文中corresponding_author 字段

CREATE TABLE aaa_cfg_object_meta(

ID  VARCHAR( 32)NOT NULL PRIMARY KEY,

NAME VARCHAR (128)

);

insert into aaa_cfg_object_meta(id ,name) select id ,(select cast ((stuff(( select author_name +',' from s_paper_author where paper_id = s_paper.id and paper_author_type_id='3' for xml path ( '')),1 , 0, '')) as varchar ( 8000 ))) as author_name

from s_paper;

update s_paper set corresponding_author=(select name from aaa_cfg_object_meta where s_paper.id =id);

drop table aaa_cfg_object_meta;