CODEWITHSUNDEEP
sql-servert-sql
Buda Florin
Buda Florin

Reputation: 666

Create temporary table with dynamic number of columns

I'm trying to create a temporary table with a dynamic number of columns:

set @cmd = ' SELECT * into #temp3 from
            (
                select * from sometable
            ) x pivot
            (
                max(buildrate)
                for name in ('+ @columns +')
            ) as y '

execute(@cmd);

select * from #temp3 left join performed in an elegant way...

and I need to use contents from that table in other processing.

Is there any reasonable way to do this?

Upvotes: 7

Views: 23484

Answers (3)

Sid Sousa
Sid Sousa

Reputation: 31

I create this script for test a dynamic temporary table.

I have Based on post in https://celedonpartners.com/blog/sql-server-how-to-a-create-temp-table-with-dynamic-column-names/

Hope this could help.

-- create and populate table
DROP TABLE dbo.sometable
GO

CREATE TABLE dbo.sometable(
    daterate datetime,
    name varchar(100),
    buildrate decimal(10,3),        
)
GO

DECLARE @COUNT INT

SET @COUNT = 0

WHILE @COUNT < 1000
BEGIN

    IF (CAST(RAND() * 2 AS INT) % 2) = 1
    BEGIN
        INSERT INTO dbo.sometable(daterate, name, buildrate) values(DATEADD(DAY, RAND() * -180, GETDATE()), 'Jeff', RAND() * 25.0)    
        INSERT INTO dbo.sometable(daterate, name, buildrate) values(DATEADD(DAY, RAND() * -180, GETDATE()),'Andrew', RAND() * 25.0)
        INSERT INTO dbo.sometable(daterate, name, buildrate) values(DATEADD(DAY, RAND() * -180, GETDATE()),'Mary', RAND() * 25.0)
        INSERT INTO dbo.sometable(daterate, name, buildrate) values(DATEADD(DAY, RAND() * -180, GETDATE()),'Carl', RAND() * 25.0)
        INSERT INTO dbo.sometable(daterate, name, buildrate) values(DATEADD(DAY, RAND() * -180, GETDATE()),'Zack', RAND()  * 25.0)
    END
    ELSE 
    BEGIN
        INSERT INTO dbo.sometable(daterate, name, buildrate) values(DATEADD(DAY, RAND() * -180, GETDATE()),'Jack', RAND()  * 50.0)
        INSERT INTO dbo.sometable(daterate, name, buildrate) values(DATEADD(DAY, RAND() * -180, GETDATE()),'Mag', RAND()  * 50.0)
        INSERT INTO dbo.sometable(daterate, name, buildrate) values(DATEADD(DAY, RAND() * -180, GETDATE()),'Kim', RAND()  * 50.0)
        INSERT INTO dbo.sometable(daterate, name, buildrate) values(DATEADD(DAY, RAND() * -180, GETDATE()),'Suse', RAND()  * 50.0)
    END    
    SET @COUNT = @COUNT + 1
END    
GO


-- execute query with the created table
DECLARE @columns VARCHAR(MAX)
      , @columns_name varchar(max)
      , @sql varchar(max)

IF OBJECT_ID('tempdb..#temp3') != 0
    DROP TABLE #temp3

SELECT 
    @columns = COALESCE(@columns + ', ', '') + '[' + name + '] float'
   ,@columns_name = COALESCE(@columns_name + ', ', '') + '[' + name + ']'
FROM (SELECT DISTINCT NAME FROM sometable) VW
order by name


CREATE TABLE #temp3(   
   daterate varchar(10)       
)

EXEC('alter table #temp3 add ' + @columns)

SET @sql = 'insert into #temp3 
            select * from
            (
                select name, buildrate, right(convert(varchar(10), daterate, 103), 7) as daterate  from sometable
            ) x pivot
            (
                max(buildrate)
                for name in ('+ @columns_name +')
            ) as y '

EXEC(@sql)

SELECT * FROM #temp3

Upvotes: 2

Muflix
Muflix

Reputation: 6768

Based on @Ocaso Protal comment, i created this

-- create global temporary table with timestamp to prevent multiple user collision
DECLARE @timestamp NVARCHAR(255) = REPLACE(REPLACE(REPLACE(REPLACE(CONVERT(NVARCHAR(23), GETDATE(), 121),'-',''),' ',''),':',''),'.','')
DECLARE @temp NVARCHAR(255) = '##temp_'+@timestamp
SET @query = 'CREATE TABLE ' + @temp + ' (RowName NVARCHAR(255), ' + @outputQueryColumns + ')'
EXECUTE sp_executesql @query

-- working with temporary table whether in procedure or in dynamic sql

-- delete global temporary table
SET @query = 'DROP TABLE ' + @temp
EXECUTE sp_executesql @query

Upvotes: 1

Lukasz Szozda
Lukasz Szozda

Reputation: 175586

One workaround is to use global temporary table:

SET @cmd = ' SELECT * INTO ##temp3 FROM
            (
                select * from sometable
            ) x pivot
            (
                max(buildrate)
                for name in ('+ @columns +')
            ) as y '

EXECUTE(@cmd);

SELECT *
INTO #temp3
FROM ##temp3;

DROP TABLE ##temp3;

SELECT *
FROM JOIN #temp3 
LEFT ...;

The normal local temporary table won't work, because Dynamic SQL creates new context. The table is in that context and will cease to exist when code is executed, so you cannot use it outside Dynamic-SQL.

Upvotes: 4

Related Questions