DECLARE @tablestring nvarchar(MAX)
SET @tablestring = (select distinct description from sometable) + ' NVARCHAR(50) '

CREATE TABLE NewTable
(
@tablestring
)

quote:

---

Originally posted by jimf

SELECT <columns from sometable>
INTO newTableWithSameNames
FROM sometable

Jim

Everyday I learn something that somebody else already knew

---

quote:

---

Originally posted by xzibited999

Hey Jim, actually looking to do the opposite of that. I'm looking to create a table where the column names are the results for the query. So for example:

select
distinct [thesearethenewcolmnnames]
from sometable

then, create a table using [thesearethenewcolumnnames] as the new column names

quote:

---

Originally posted by jimf

SELECT <columns from sometable>
INTO newTableWithSameNames
FROM sometable

Jim

Everyday I learn something that somebody else already knew

---

---

select distinct Type 
INTO newtabletest
from Agreements

quote:

---

Originally posted by visakh16

quote:

---

Originally posted by xzibited999

Hey Jim, actually looking to do the opposite of that. I'm looking to create a table where the column names are the results for the query. So for example:

select
distinct [thesearethenewcolmnnames]
from sometable

then, create a table using [thesearethenewcolumnnames] as the new column names

quote:

---

Originally posted by jimf

SELECT <columns from sometable>
INTO newTableWithSameNames
FROM sometable

Jim

Everyday I learn something that somebody else already knew

---

---

thats exactly what Jim's suggestion does

Try it out and you'll have new table created with columns of resultset

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---

quote:

---

Originally posted by xzibited999

Guys, maybe I'm losing my mind here, but I went ahead and tested that "just" to be sure, and its creating a new table with one column...and 36 new rows created. Those 36 rows should be column names. Here's my SQL statement:

select distinct Type 
INTO newtabletest
from Agreements

looking at newtabletest, it shows one column named "type", and then the 36 agreements are rows beneath that "type" column.

quote:

---

Originally posted by visakh16

quote:

---

Originally posted by xzibited999

Hey Jim, actually looking to do the opposite of that. I'm looking to create a table where the column names are the results for the query. So for example:

select
distinct [thesearethenewcolmnnames]
from sometable

then, create a table using [thesearethenewcolumnnames] as the new column names

quote:

---

Originally posted by jimf

SELECT <columns from sometable>
INTO newTableWithSameNames
FROM sometable

Jim

Everyday I learn something that somebody else already knew

---

---

thats exactly what Jim's suggestion does

Try it out and you'll have new table created with columns of resultset

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---

---

quote:

---

Originally posted by mrsqlol

I wrote the script at work today, but can't post to forums from there. You want something like:

DECLARE @string varchar(1000)

SELECT @string = COALESCE(@string, '') + [insertcolumnname] + ' varchar(50) NULL,'
FROM [insert table name]

SET @string = SUBSTRING(@string, 1, LEN(@string)-1) //to get rid of the ending ,

EXEC('CREATE TABLE [newtablename] ('+@string+') ')

This won't get you distinct values, and it won't guarantee that the names you are pulling in meet the requirements of a proper column name, but this is a good starting point for you.

---