SQL - Dynamically add columns to temp table based on value

Question

I need to perform some dynamic operation temp table based on some condition. (add columns based of number and update only those columns)

I have one one master table which contains unit information - E.G.

ID   UNIT
1     kg
2     cm
3     mm

Here, number of rows can vary. It can contain 3 rows or 4 rows or 2 rows.

Now i want create some columns in my temp table based on this.

E.G.

if master table has 2 values then #temp should contain 2 columns as unit1 and unit2. if 3 values then unit1, unit2 and unit3.

Is it possible? Or do i need to create max number of columns directly in temp table?

Thanks

Answer 1

You have to use Dynamic PIVOT and GLOBAL TEMP TABLE in following:

IF OBJECT_ID('tempdb..#t') IS NOT NULL 
DROP TABLE #t
GO

CREATE  table #t 
(id varchar(max),unit varchar(max))
insert into #t (id,unit)values 
(1,'kg'),
(2,'cm'),
(3,'mm'),
(4,'m')

DECLARE @statement NVARCHAR(max)
       ,@columns NVARCHAR(max)

SELECT @columns = ISNULL(@columns + ',', '') + N'[' + cast(tbl.id as varchar(max)) + ']'
FROM (
   SELECT DISTINCT id
   FROM #t
   ) AS tbl

SELECT @statement =  'select * 
                      INTO ##temp 
                      from (
                        SELECT id,[unit]
                        FROM #t
                        ) as s 
PIVOT
(max(unit) FOR id in(' + @columns + ')) as pvt
'


EXEC sp_executesql @statement = @statement

SELECT * FROM ##temp

DROP TABLE ##temp