Insert id, name of column and value into a Table SQL Server

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.COLU开发者_如何学PythonMNS   
WHERE TABLE_NAME = 'ORIGINALTABLE' ORDER BY id,  [value] = ... 

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). 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. Or in Celko's fantastic Sql For Smarties Book

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