Author |
Topic |
xzibited999
Starting Member
6 Posts |
Posted - 2012-08-27 : 15:03:24
|
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
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2012-08-27 : 15:27:14
|
SELECT <columns from sometable>INTO newTableWithSameNamesFROM sometableJimEveryday I learn something that somebody else already knew |
|
|
xzibited999
Starting Member
6 Posts |
Posted - 2012-08-27 : 16:03:32
|
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:selectdistinct [thesearethenewcolmnnames]from sometablethen, create a table using [thesearethenewcolumnnames] as the new column namesquote: Originally posted by jimf SELECT <columns from sometable>INTO newTableWithSameNamesFROM sometableJimEveryday I learn something that somebody else already knew
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-08-27 : 17:44:59
|
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:selectdistinct [thesearethenewcolmnnames]from sometablethen, create a table using [thesearethenewcolumnnames] as the new column namesquote: Originally posted by jimf SELECT <columns from sometable>INTO newTableWithSameNamesFROM sometableJimEveryday I learn something that somebody else already knew
thats exactly what Jim's suggestion doesTry it out and you'll have new table created with columns of resultset------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
xzibited999
Starting Member
6 Posts |
Posted - 2012-08-28 : 11:37:56
|
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 newtabletestfrom 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:selectdistinct [thesearethenewcolmnnames]from sometablethen, create a table using [thesearethenewcolumnnames] as the new column namesquote: Originally posted by jimf SELECT <columns from sometable>INTO newTableWithSameNamesFROM sometableJimEveryday I learn something that somebody else already knew
thats exactly what Jim's suggestion doesTry it out and you'll have new table created with columns of resultset------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
|
|
|
mrsqlol
Starting Member
1 Post |
Posted - 2012-08-28 : 20:13:37
|
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. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-08-28 : 21:52:26
|
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 newtabletestfrom 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:selectdistinct [thesearethenewcolmnnames]from sometablethen, create a table using [thesearethenewcolumnnames] as the new column namesquote: Originally posted by jimf SELECT <columns from sometable>INTO newTableWithSameNamesFROM sometableJimEveryday I learn something that somebody else already knew
thats exactly what Jim's suggestion doesTry it out and you'll have new table created with columns of resultset------------------------------------------------------------------------------------------------------SQL Server MVPhttp://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 MVPhttp://visakhm.blogspot.com/ |
|
|
xzibited999
Starting Member
6 Posts |
Posted - 2012-08-29 : 11:26:42
|
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.
|
|
|
|