Question: I have a result table with one row and some columns:

```
column1  column2  column3   column4   column5   column6   column7  column8   column9
------------------------------------------------------------------------------------
   1.4       2.2      3.4       6.57      5.6       9.7      67.6      3.4      5.9  

```

I have a table like:

```
DECLARE @TTable TABLE(
 ID INT,
 Name VARCHAR(100),
 value FLOAT 
)

```

I want to have something like

```
ID   Name      value
----------------------
1    column1    1.4         
2    column2    2.2 
3    column3    3.4
4    column4    6.57
5    column5    5.6
6    column6    9.7
7    column7    67.6
8    column8    3.4
9    column9    5.9

```

So I am doing something like:

```
INSERT @TTable   
SELECT [id]  = ORDINAL_POSITION,  
[Name] = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS   
WHERE TABLE_NAME = 'ORIGINALTABLE' ORDER BY id   

```

this results with 2 fields '`id`', and '`Name`' of column, but How would you add the `value` field?

If I add a `SELECT` to the query: 

```
INSERT @TTable   
SELECT [id]  = ORDINAL_POSITION,  
[Name] = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS   
WHERE TABLE_NAME = 'ORIGINALTABLE' ORDER BY id,  [value] = ... 

```

Answer: 
If you are using SQL Server 2008, the you can use `UNPIVOT` and dynamic SQL so you don't have to write every column on your own (Before using dynamic SQL take a look at this [link](http://www.sommarskog.se/dynamic_sql.html)). Try this:

**UPDATED after comments**

```
DECLARE @Columns NVARCHAR(MAX)='', @Query NVARCHAR(MAX)=''
DECLARE @CastColumns NVARCHAR(MAX)=''

SELECT  @Columns = @Columns + QUOTENAME(COLUMN_NAME) + ',',
        @CastColumns = @CastColumns+CASE WHEN data_type <> 'float' THEN
        'CAST('+QUOTENAME(COLUMN_NAME)+' AS FLOAT) AS '+QUOTENAME(COLUMN_NAME) ELSE
        QUOTENAME(COLUMN_NAME) END+','
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'YourTable' 
ORDER BY ORDINAL_POSITION

SET @Columns = LEFT(@Columns,LEN(@Columns)-1)
SET @CastColumns = LEFT(@CastColumns,LEN(@CastColumns)-1)

SET @Query = '
SELECT ROW_NUMBER() OVER(ORDER BY CO.Ordinal_Position) Id, ColumnName, Value
FROM (SELECT '+@CastColumns+' FROM YourTable) AS P
UNPIVOT(value FOR ColumnName IN ('+@Columns+')) AS UC
JOIN (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ''YourTable'') CO
ON ColumnName = CO.COLUMN_NAME
'

INSERT INTO @TTABLE
EXEC sp_executesql @Query

```

Ok, now I changed the query so it does a `CAST` to FLOAT over the columns that are not already FLOAT. Let me know how it goes.
You need to do a cross-tab query to transform from a row with many columns to a number of rows with a couple of columns, good example [in this article](http://www.simple-talk.com/sql/t-sql-programming/creating-cross-tab-queries-and-pivot-tables-in-sql/). Or in [Celko's fantastic Sql For Smarties Book](http://books.google.co.uk/books?id=Hi9fMnOoRtAC&pg=PA542&lpg=PA542&dq=cross+tab+query+celko&source=bl&ots=tsdV-aV7Tr&sig=Rd-qUNm8pCzPq_VCOCU9vhHKCJM&hl=en&ei=W9lrTeelB82BhQebif3xDg&sa=X&oi=book_result&ct=result&resnum=6&ved=0CEAQ6AEwBQ#v=onepage&q&f=false)

Basically you do a series of case statements that transform the table by pivoting it on an axis.