Creating a table from the Rows of Other table

How can I create table from the rows of other table?

My requirement is I have a table test which has a column Abc with some values say a,b,c,d,e

Is it possible to create a table which has the column names as a,b,c,d,e...

Since the rows in the test table are dynamic...is it possible to create a table with the dynamic columns?

Any pointers in this regard?

Yes.. You can do this. Use INTO clause on your Select Statement.

Select A,B,C,D,E INTO NEWTABLE from ABC

|||

Thanks Sekaran.

But my problem is I am not sure the number of rows in my first table.

i,e if I do Select * from temp and it it returns 10 rows then those 10 rows should be the column names in my second table.and if there are only 5 rows then my second table should have 5 columns only.

|||

Ok.. You want to create table using your Rows..

I am not sure why you need this.. This is not good idea to create a table on the fly.

Are you want to convert the Row wise data into column? Something like PIVOT table.

Give more info...

|||

tried with PIVOT it doesnt seem to work out .. as i dont have an INTEGER on which i can pivot

and i dont know what would my for() will have.

see this is my case:

I've a table A with columns a1,a2,a3

i've table B with column b1 and values a4,a5 (offcourse the number of rows in b1 always vary)

select a1,a2,a3 from A

pivot

max(?)

for ([?],[?].....)

order by ?

and above all.....I'm just trying to create the schema and surely not going for the population at this moment

awaiting for your quick reply

|||

I've a table A with columns a1,a2,a3

i've table B with column b1 and values a4,a5 (offcourse the number of rows in B always vary)

and my resulting table C should be having a1,a2,a3,a4,a5 columns

|||

If I understand you want the table to have the same schema as table A but in addition to also have the data values in table B as additional columns?

The only way I can think is to create the table using dynamic sql using syscolumns to generate the first part of the SQL and then cursoring through the datavalues in table be to generate the remaining SQL.

I'm not sure why you would do this but if you need to then I suggest that you strictly control the entries in table B.

|||

Thanks Sunny,

But there is no way that I can restrict the entries in table B. But at the max there will be 30-40 rows which needs to be changed as the header for other report.

Can trigger help me in my case?

|||

Still your problem is not clear, help us understand in better way.

Pls put some proper sample data rather A,B,C & a1, a2 ...

Its confusing buddy..

|||

Here is the example code:

Create table Meta(Columnname varchar(20))

Insert into Meta values('EmpNo')

Insert into Meta values('EmpName')

Insert into Meta values('Address')

Now if I do select * from Meta the result will be

EmpNo

EmpName

Address

Then I have another table Employee with two columns(Tel# ,SSN)

So my requirement is to change the schema of Employee table as (EmpNo,EmpName,Address,Tel#,SSN)

This is just an example as the number of rows in the Meta table is not known.

|||

Ok. You want to change the database (table) structure when you insert any new column on META table.

I don't recommand this. This is not a good practice at all.

If there is any schema change it should be done via proper script & by one hand(most of the time DBA).

I am really not sure why this dangerous logic you took in your hand.

If you ask me strightly its possible to do via trigger... But take care, take care on Update/Delete of your META data.

You may mess-up lot of dependent SPs, Views, Functions & even on your UI .

Create Trigger Meta_Trigger On Meta

For Insert

as

Begin

Declare @.Q as varchar(1000);

Select @.Q ='Alter Table Employee Add ' + ColumnName + ' Varchar(1000)' From Inserted;

Exec(@.Q)

End

|||

sekharan....how will i get the "columnname" in the above case

my problem was always getting the variable name here!!!

|||Whenever you insert the new column from your variable to the Meta table the trigger will find the newly inserted value using the INSERTED spl table...