Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Results from select for column names of new table
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

xzibited999
Starting Member

6 Posts

Posted - 08/27/2012 :  15:03:24  Show Profile  Reply with Quote
All, I've been trying (unsuccessfully) to create a new table with column names based on the results of a query set. Here's what I have so far, you should be able to get where I'm going with this. Any thoughts?


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

CREATE TABLE NewTable
(
@tablestring
)

jimf
Flowing Fount of Yak Knowledge

USA
2875 Posts

Posted - 08/27/2012 :  15:27:14  Show Profile  Reply with Quote
SELECT <columns from sometable>
INTO newTableWithSameNames
FROM sometable

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

xzibited999
Starting Member

6 Posts

Posted - 08/27/2012 :  16:03:32  Show Profile  Reply with Quote
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

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 08/27/2012 :  17:44:59  Show Profile  Reply with Quote
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/

Go to Top of Page

xzibited999
Starting Member

6 Posts

Posted - 08/28/2012 :  11:37:56  Show Profile  Reply with Quote
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/



Go to Top of Page

mrsqlol
Starting Member

1 Posts

Posted - 08/28/2012 :  20:13:37  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 08/28/2012 :  21:52:26  Show Profile  Reply with Quote
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/






so were you expecting this ?

http://beyondrelational.com/modules/2/blogs/70/posts/10840/dynamic-pivot-in-sql-server-2005.aspx

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

Go to Top of Page

xzibited999
Starting Member

6 Posts

Posted - 08/29/2012 :  11:26:42  Show Profile  Reply with Quote
Nice! This is a great starting point. Thanks mrsqlol.

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.

Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000