1.数据准备
create table Vertical(
Id int ,
ProjectName varchar(20),
ProjectValue int
)
insert into Vertical values (101,'旅游',100)
insert into Vertical values (101,'牧业',101)
insert into Vertical values (101,'工业',102)
insert into Vertical values (101,'软件',103)
insert into Vertical values (102,'旅游',200)
insert into Vertical values (102,'牧业',201)
insert into Vertical values (102,'工业',202)
insert into Vertical values (102,'软件',203)
select *from Vertical
--返回格式:
[{'Id':'101','旅游':'100','牧业':'101','工业':'102','软件':'103'},{'Id':'102','旅游':'200','牧业':'201','工业':'202','软件':'203'}]
Create proc VerticaltoHorizontal
@tbname varchar(20),
@returnmsg varchar(1000) out
as
begin
declare @id int
declare @name varchar(20)
declare @value int
declare @strjson varchar(1000)
declare @idintentity int =0
declare v2h cursor
for select id,ProjectName,projectvalue from vertical
open v2h
fetch v2h into @id,@name,@value
while @@FETCH_STATUS=0
begin
if(@idintentity=0)
set @strjson='{''Id'':'''+CAST(@id as varchar(10))+''','''+@name+''':'''+CAST(@value as varchar(10))+''''
else
begin
if (@idintentity= @id)
begin
set @strjson=@strjson+','''+@name+''':'''+CAST(@value as varchar(10))+''''
end
else
begin
set @strjson =@strjson+'},'
set @strjson=@strjson+'{''Id'':'''+CAST(@id as varchar(10))+''','''+@name+''':'''+CAST(@value as varchar(10))+''''
end
end
set @idintentity= @id
fetch v2h into @id,@name,@value
end
close v2h
deallocate v2h
set @returnmsg ='['+ @strjson+'}]'
end
go
备注:竖转横 的表的实例很多,我建议还是在程序中处理比较好