首页 > Sql-Mysql > sql列变行 活用游标实现列数据变行数据

sql列变行 活用游标实现列数据变行数据

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

首先,通过联合两个表时,查询得到的是如下的数据
其中各个属性名称都在 “属性”列中,值在其对应的值列中;

sql图1

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

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

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

————————————————————————–
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

  1. 还没有评论
评论提交中, 请稍候...

留言

可以使用的标签: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>
Trackbacks & Pingbacks ( 0 )
  1. 还没有 trackbacks