Dynamic Row Values to Column Headers in SQL Server with out using PIVOT Table

Dear Friends,

         My Friend asked me a question that he has a requirement that he wants to display the Row Values to Column Headers that to dynamically in SQL Server with out using the PIVOT Table.

He shown me the requirement like this....
Recordid  vchname  vchsubject  nummarks
1 Havi English 94.00
2 Havi Hindi 96.00
3 Havi Telugu 89.00
4 Havi Maths 99.00
5 Kruth Maths 96.00
6 Kruth Physics 92.00
7 Kruth Biology 78.00
8 Kruth Social 61.00
9 Yukku English 48.00
10 Yukku Hindi 56.00
11 Yukku English 94.00
12 Yukku Computers 99.00
13 Mahi Telugu 78.00
14 Mahi Hindi 76.00
15 Mahi English 94.00
16 Mahi Social 79.00
17 Mani Telugu 86.00
18 Mani Physics 95.00
19 Mani Biology 93.00
20 Mani English 91.00

The result should be like this....
rec   name      Bio  comp eng hind mat  phy  soc  tel
1 Havi 0 0 94 96 99 0 0 89
2 Kruth 78 0 0 0 96 92 61 0
3 Mahi 0 0 94 76 0 0 79 78
4 Mani 93 0 91 0 0 95 0 86
5 Yukku 0 99 48 56 0 0 0 0

For this i wrote a procedure... Please go through it and this may be helpful for you who is searching for this from long time....

--------------------------------------------------------------------------------------------------------------

create database kish
use kish

create table tabStuinfo
(recordid bigint identity(1,1) primary key,
 vchname varchar(20),
 vchsubject varchar(20),
 nummarks numeric(12,2)
 )

 insert into tabStuinfo(vchname,vchsubject,nummarks) values('Havi','English',94);
 insert into tabStuinfo(vchname,vchsubject,nummarks) values('Havi','Hindi',96);
 insert into tabStuinfo(vchname,vchsubject,nummarks) values('Havi','Telugu',89);
 insert into tabStuinfo(vchname,vchsubject,nummarks) values('Havi','Maths',99);

 insert into tabStuinfo(vchname,vchsubject,nummarks) values('Kruth','Maths',96);
 insert into tabStuinfo(vchname,vchsubject,nummarks) values('Kruth','Physics',92);
 insert into tabStuinfo(vchname,vchsubject,nummarks) values('Kruth','Biology',78);
 insert into tabStuinfo(vchname,vchsubject,nummarks) values('Kruth','Social',61);

 insert into tabStuinfo(vchname,vchsubject,nummarks) values('Yukku','English',48);
 insert into tabStuinfo(vchname,vchsubject,nummarks) values('Yukku','Hindi',56);
 insert into tabStuinfo(vchname,vchsubject,nummarks) values('Yukku','English',94);
 insert into tabStuinfo(vchname,vchsubject,nummarks) values('Yukku','Computers',99);

 insert into tabStuinfo(vchname,vchsubject,nummarks) values('Mahi','Telugu',78);
 insert into tabStuinfo(vchname,vchsubject,nummarks) values('Mahi','Hindi',76);
 insert into tabStuinfo(vchname,vchsubject,nummarks) values('Mahi','English',94);
 insert into tabStuinfo(vchname,vchsubject,nummarks) values('Mahi','Social',79);

 insert into tabStuinfo(vchname,vchsubject,nummarks) values('Mani','Telugu',86);
 insert into tabStuinfo(vchname,vchsubject,nummarks) values('Mani','Physics',95);
 insert into tabStuinfo(vchname,vchsubject,nummarks) values('Mani','Biology',93);
 insert into tabStuinfo(vchname,vchsubject,nummarks) values('Mani','English',91);

 select * from tabStuinfo


ALTER proc [dbo].[procStudentinfo]
as
begin
declare @subcount int=0;
declare @tableVar Table(recid int,colname varchar(30));
declare @UpdatetableVar Table(recid int,vchname varchar(30),vchsubject varchar(30),nummarks numeric);
declare @counter int=1;
declare @Query nvarchar(max)=',';
declare @SubCol nvarchar(max)=null;
declare @totalQuery nvarchar(max);

select @subcount=count(distinct vchsubject) from tabstuinfo;
insert @tableVar(recid, colname) select distinct dense_rank() OVER (Order by vchsubject), vchsubject from tabstuinfo;

WHILE (@COUNTER <= @subcount)
BEGIN
    select @SubCol=colname from @tableVar where recid=@COUNTER
    set @Query=@Query + '"'+@SubCol+'"'+' numeric default 0,';
    SET @COUNTER = @COUNTER + 1
END

select @Query=left (right (@Query, len (@Query)-1), len (@Query)-2);
PRINT @Query;

create table #temptab(recid int identity(1,1),vchname varchar(30));

SET @totalQuery='ALTER TABLE #temptab ADD '+@Query;
PRINT @totalQuery;

EXEC sp_executesql @totalQuery;

insert into #temptab(vchname) select distinct vchname from tabStuinfo;

DECLARE @TEMPCOUNT INT=0;
DECLARE @STUMARKS varchar(30)=NULL;
DECLARE @StuName VARCHAR(30)=NULL;
DECLARE @RECCOUNT INT=0;
select @TEMPCOUNT=COUNT(*) from #temptab
select @RECCOUNT=COUNT(*) from tabStuinfo;
insert @UpdatetableVar(recid, vchname, vchsubject,nummarks) select row_number() OVER (Order by vchname,vchsubject),vchname,vchsubject,nummarks from tabstuinfo ;

SET @COUNTER=1;

DECLARE @NewCounter int=1;

WHILE(@NewCounter<=@TEMPCOUNT)
BEGIN
WHILE (@COUNTER <= @RECCOUNT)
BEGIN
select @SubCol=vchsubject,@StuName=vchname,@STUMARKS=nummarks  from @UpdatetableVar where recid=@COUNTER;
--PRINT 'SUB NAME : '+@SubCol;
--PRINT 'STUDENT NAME : '+@StuName;
--PRINT @STUMARKS;
--PRINT 'UPDATED STUDENT ' + ''+@StuName+'';
exec('UPDATE #temptab SET "'+@SubCol+'"=' + @STUMARKS + ' WHERE vchname='''+@StuName+'''');
SET @COUNTER = @COUNTER + 1
END
SET @NewCounter=@NewCounter+1
END

SELECT * FROM #temptab;

end

exec procStudentinfo
select * from tabStuinfo

insert into tabStuinfo ('Havi','General Knowledge',99);
exec procStudentinfo