June 15, 2010 at 2:08 pm
select distinct LastName
into #temp
from Members
my results (for example) would be
jones
smith
edwards
mathews
I would like to dynamically create a table based on those results...
CREATE TABLE #Locations
(
jones nvarchar(250),
smith nvarchar(250),
edwards nvarchar(250),
mathews nvarchar(250)
)
How can I make my column names populate based on the results of my temp table?
CREATE TABLE #Locations
(
select lastname from #temp
)
This code obviously does not work, but is basically what I'm trying to do
June 15, 2010 at 2:26 pm
It would require dynamic SQL to get this to work. The catch is that the temp table will be scoped to the EXEC statement so you'd need to create the table as a global temporary table.
I would recommend coming up with a better solution. You don't want to deal with the headaches caused by global temp tables. If you have to jump through that many hoops to get this to work, that would be a red flag to me that you should take a step back and reevaluate your solution.
What are you trying to accomplish with your #Locations table? I have a feeling we can get this done w/o temp tables.
June 15, 2010 at 2:47 pm
After re-evaluating i definitely need a new approach. Thanks for the info.
June 15, 2010 at 2:48 pm
I agree with john but here is a very generic script all the same that would do the job.
Declare @stmt nvarchar(500)
set @stmt='create table ##Locations ('
select @stmt = @stmt + LastName +' nvarchar(250),'
from #temp
--Finish up the temp and include a last temp column
select @stmt = @stmt +'EndCol char(1))'
exec sp_executesql @stmt
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
June 15, 2010 at 2:52 pm
jmyren (6/15/2010)
After re-evaluating i definitely need a new approach. Thanks for the info.
If you can describe what you are trying to do, we can help you come up with a solid solution. This looks like a pivot to me, what type of data were you going to put into the #Locations temp table in each of the columns?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply