CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008
edgarmtze
edgarmtze

Reputation: 25058

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.COLUMNS   
WHERE TABLE_NAME = 'ORIGINALTABLE' ORDER BY id,  [value] = ...

Upvotes: 1

Views: 2643

Answers (2)

Lamak
Lamak

Reputation: 70678

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.

Upvotes: 2

amelvin
amelvin

Reputation: 9061

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.

Upvotes: 1

Related Questions