One of my friend asked me :
How to get the Columns Name of Table as a First row of Result Table?
Ex: Table is:
and the result should be:
So Here is the answer for this: I have a table called StateMaster in my database and to get the above result below is the query.
Create table #tbl (col1 varchar(100),col2 varchar(100),col3 varchar(100))
INSERT INTO #tbl(col1,col2,col3)
VALUES(NULL,NULL,NULL)
DECLARE @name varchar(50),
@i int
set @i=1;
DECLARE tblcur CURSOR STATIC LOCAL FOR
SELECT column_name from information_schema.columns
WHERE table_name = 'StateMaster' --Do not use schamea prefix (dbo.) here.
ORDER BY ordinal_position
OPEN tblcur
WHILE 1 = 1
BEGIN
FETCH tblcur INTO @name
IF @@fetch_status <> 0
BREAK
Declare @sql varchar(max)
set @sql='UPDATE #tbl
set col'+Cast(@i as varchar)+'='''+@name+''''
exec (@sql)
set @i=@i+1
END
DEALLOCATE tblcur
INSERT INTO #tbl(col1,col2,col3)
Select * from dbo.StateMaster
select * from #tbl
drop table #tbl
