SQL竖表转横表Json数据

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