本来是mysql数据库的,不过对mysql数据库的游标实在是不熟,所以先把数据导到了mssql 上,才进行了处理.

首先,通过联合两个表时,查询得到的是如下的数据

其中各个属性名称都在 “属性”列中,值在其对应的值列中;

sql图1[]1

现在需要将各个属性名称放到行中,最后到处结果到excel中。

所以需要进行处理,经过思考,我觉得只有用游标才可以实现,所以使用游标进行了处理,最后处理出的结果如下:

[]2

所使用的游标代码如下(为了安全考虑,替掉了真实的表名):

————————————————————————–

Declare @tmpIdent int;

Declare @tmpidentv varchar(20);

declare @tmpName varchar(100);

declare @tmpEmail varchar(100);

declare @tmpType varchar(100);

declare @tmpVar varchar(100);

declare @tmpvalue varchar(100);

CREATE  TABLE #tmp_cioall

(

ident int,

[name] varchar(128),

email varchar(128),

user_type varchar(128),

emailaddress varchar(128),

Gender varchar(128),

industry varchar(128),

jobtitle varchar(128),

organisation varchar(128),

town varchar(128),

birth_date varchar(128),

minibio varchar(128),

skills varchar(128),

interests varchar(128),

mainproject varchar(128),

jobdescription varchar(200)

);

declare cur1 CURSOR FOR SELECT  b.ident,b.name,b.email,b.user_type,a.name as 属性名,cast(a.value as varchar) as 值 FROM t1 a,t2 b where a.owner=b.ident order by b.ident asc;

Open cur1

Fetch next From cur1

Into @tmpIdent,@tmpName,@tmpEmail,@tmpType,@tmpVar,@tmpvalue

While(@@Fetch_Status = 0)

Begin

begin

if not exists (select * from #tmp_cioall where ident=@tmpIdent) begin

insert into #tmp_cioall(ident,[name],email,user_type) values(@tmpIdent,@tmpName,@tmpEmail,@tmpType)

end

else

begin

set @tmpidentv=cast(@tmpIdent as varchar(20))

exec (‘update #tmp_cioall set ‘+ @tmpVar+’=isnull(”’+@tmpValue+”’,””) where ident=’+@tmpidentv+”)

end

–end if

end

Fetch next From cur1

Into @tmpIdent,@tmpName,@tmpEmail,@tmpType,@tmpVar,@tmpvalue

End

Close cur1

Deallocate cur1

select ident,[name] as 姓名,email as 邮箱,user_type as 用户类型,Gender as 性别,industry as 行业, jobtitle as 头衔,organisation as “公司/组织”,town as 城市,minibio as 简单介绍,skills as 技能, interests as 爱好,mainproject as 曾经主持过的IT项目,birth_date as 生日,jobdescription as 工作描述 from #tmp_cioall

drop table #tmp_cioall