Create table using rows from another table as columns

Question

I have a table with the following structure :

TableNo1

Field1

row1

row2

row3

row4

row5

...

row n

Now I need to create a new table with the following schema:

TableNo2

Field1(row1 of Table1) Field2(row2 of Table1) Field3(row3 of table1) Fieldn(row n of table1)

I read about this but only thing i was able to find is the into clause which doesnt work.

Can anyone please help?

see [http://stackoverflow.com/questions/24470/sql-server-pivot-examples][1] — 4b0, Aug 26 '11 at 11:38
I wanna create a new table!!!!! The rows of table1 will be the columns of table2.... — Kameron, Aug 26 '11 at 11:49

score 4 · Accepted Answer · answered Aug 26 '11 at 11:56

You can use dynamic SQL

DECLARE @TableNo1 TABLE(Field varchar(128),DataType varchar(128))
DECLARE @s nvarchar(max)='CREATE TABLE dbo.TableNo2('
INSERT INTO @TableNo1
VALUES
('Field1','nvarchar(max)'),
('Field2','int')

SELECT  @s+=T.Field+' '+T.DataType+',' FROM   @TableNo1 T
SET @s=LEFT(@s,LEN(@s)-1)+')'
EXECUTE(@s)

Create table using rows from another table as columns

1 Answers1