Dynamic Columns in Temp Table

[Lbob]

Hi

I'm trying to create a temporary table that's made up of a number of dynamic columns, but I can't get it working.

This is what I've got so far

DECLARE @LT varchar(100)

SET NOCOUNT ON

CREATE TABLE #AdminSummary
(
UserID int null
)


DECLARE cursor_lt CURSOR FOR
SELECT lt_name FROM lt WHERE loc_id = @LocID

OPEN cursor_lt
FETCH NEXT FROM cursor_lt INTO @LT
WHILE @@fetch_status = 0
BEGIN
ALTER TABLE #AdminSummary ADD @LT varchar(100) NULL
GO

FETCH NEXT FROM cursor_lt INTO @LT
END
CLOSE cursor_lt
DEALLOCATE cursor_lt


Can anyone help?
Cheers
Lbob

 

[SQLDenis]

GO is a batch terminator and can't be used this way

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLDenis]

I don't think that you can do it like this
use dynamic SQL and create a 'real' table append NEWID() to make tablename unique
and then drop that after you are done

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[Lbob]

Ok, is there another way of creating dynamic columns ?

 

[SQLDenis]

dynamic SQL and create a real table

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[Lbob]

What do you mean? The problem is I don't know how many columns there are going to be?

 

[Lbob]

I've worked it out.....
Here's how to do it

DECLARE @LT varchar(100)

SET NOCOUNT ON

CREATE TABLE #AdminSummary
(
UserID int null
)


DECLARE cursor_lt CURSOR FOR
SELECT lt_name FROM lt WHERE loc_id = @LocID

OPEN cursor_lt
FETCH NEXT FROM cursor_lt INTO @LT
WHILE @@fetch_status = 0
BEGIN
EXEC( 'ALTER TABLE #AdminSummary ADD [' + @LT + '] varchar(100)' )

FETCH NEXT FROM cursor_lt INTO @LT
END
CLOSE cursor_lt
DEALLOCATE cursor_lt

 

[SQLDenis]

That could work although you might run into problems, I have had problems and the error was column is not available when trying to do an insert., but you could use it in a select???

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/