SQL Server Forums
Profile | Register | 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
 New Topic  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
2869 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
52325 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
52325 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  
 New 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.09 seconds. Powered By: Snitz Forums 2000