Use Below script

-------------------Script start here --------------------

DECLARE @Table nvarchar(50), @NumCols int, @NumRows int, @n int, @strSQL
nvarchar(300)
--DROP TABLE #Temp
Set @Table = 'r1'
Set @n = 0
Set @NumCols = (SELECT COUNT(*) FROM r1)
Set @NumRows = (SELECT COUNT(*) FROM syscolumns WHERE ID = (SELECT ID FROM
sysobjects WHERE name = @Table))

CREATE TABLE #Temp(
Col1 nvarchar(50)
)
INSERT INTO #Temp SELECT [Name] FROM syscolumns WHERE ID = (SELECT ID FROM
sysobjects WHERE name = @Table)

WHILE @n < @NumCols
BEGIN
Set @strSQL = 'ALTER TABLE #Temp ADD Col' + CONVERT(nvarchar(10),@n+2) + '
nvarchar(50) NULL'
PRINT (@strSQL)
EXEC (@strSQL)
Set @n = @n + 1
END
GO

/* To Insert the values in Name1 */
DECLARE @Value varchar(250)
DECLARE @iCol INT
DECLARE @strAddQry NVARCHAR(4000)
SET @iCol = 2

DECLARE Name1_cursor CURSOR FOR
Select Name1 from r1
OPEN Name1_cursor

FETCH NEXT FROM Name1_cursor
INTO @Value
WHILE @@FETCH_STATUS = 0
BEGIN
SET @strAddQry = 'UPDATE #Temp SET Col' + cast(@iCol as varchar) + '='''
+ @Value + ''' WHERE Col1= ''' + 'Name1' + ''''
PRINT (@strAddQry)
EXEC (@strAddQry)
SET @iCol = @iCol + 1
FETCH NEXT FROM Name1_cursor
INTO @Value
END

CLOSE Name1_cursor
DEALLOCATE Name1_cursor

/* To Insert the values in Address */
SET @iCol = 2

DECLARE address_cursor CURSOR FOR
Select address from r1
OPEN address_cursor

FETCH NEXT FROM address_cursor
INTO @Value
WHILE @@FETCH_STATUS = 0
BEGIN
SET @strAddQry = 'UPDATE #Temp SET Col' + cast(@iCol as varchar) + '='''
+ @Value + ''' WHERE Col1= ''' + 'address' + ''''
PRINT (@strAddQry)
EXEC (@strAddQry)
SET @iCol = @iCol + 1
FETCH NEXT FROM address_cursor
INTO @Value
END

CLOSE address_cursor
DEALLOCATE address_cursor

/* To Insert the values in roll */
SET @iCol = 2

DECLARE roll_cursor CURSOR FOR
Select roll from r1
OPEN roll_cursor

FETCH NEXT FROM roll_cursor
INTO @Value
WHILE @@FETCH_STATUS = 0
BEGIN
SET @strAddQry = 'UPDATE #Temp SET Col' + cast(@iCol as varchar) + '='''
+ @Value + ''' WHERE Col1= ''' + 'roll' + ''''
PRINT (@strAddQry)
EXEC (@strAddQry)
SET @iCol = @iCol + 1
FETCH NEXT FROM roll_cursor
INTO @Value
END

CLOSE roll_cursor
DEALLOCATE roll_cursor

SELECT * FROM #Temp

-------------------Script Ends here --------------------


MS Sql Server LazyDBA home page