SQL management studio 2005: Creating a temp pivot table

I used the following dynamic SQL to create a temp pivot table in a script to join onto my final result set. I used this method as the names of the columns could change depending on user input.
It involves creating a temp table then adding columns and populating the table using dynamic SQL in  a loop.




DECLARE @work_order int
--create temp function table first
DECLARE @function_table TABLE (primary_key INT IDENTITY(1,1) NOT NULL,
[Function] varchar(100))

DECLARE @item_category_counter INT
DECLARE @loop_counter INT

INSERT INTO @function_table
SELECT wo_team_label_desc FROM ref_wo_team WHERE dept_id = @dept_id AND
inactive_ind = 0 ORDER BY sort_order

--Pivot function table
CREATE TABLE #PIVOT_FUNCTON_TABLE (primary_key INT IDENTITY(1,1) NOT NULL,
work_order int)

SET  @loop_counter = ISNULL((SELECT COUNT(*) FROM @function_table),0)
-- Set the @loop_counter to the total number of rows in the memory table

SET @item_category_counter = 1

WHILE @loop_counter > 0 AND @item_category_counter <= @loop_counter
BEGIN

--Add dynamic column name
DECLARE @ColumnName NVARCHAR(100)
SET @ColumnName = (select [Function] FROM @function_table WHERE primary_key
= @item_category_counter)
DECLARE @query NVARCHAR(4000)
SET  @query = 'ALTER TABLE #PIVOT_FUNCTON_TABLE
ADD [' + @ColumnName + ']  NVARCHAR(100);'
EXECUTE(@query)

SET @item_category_counter = @item_category_counter + 1
END

DECLARE @item_category_counterWO INT
DECLARE @loop_counterWO INT

SET  @loop_counterWO = ISNULL((SELECT COUNT(*) FROM @WOtable),0)
-- Set the @loop_counter to the total number of rows in the memory table
SET @item_category_counterWO = 1

WHILE @loop_counterWO > 0 AND @item_category_counterWO <= @loop_counterWO
BEGIN

SET @work_order = (SELECT work_order_id FROM @WOtable WHERE primary_key =
@item_category_counterWO)

--Populate table
DECLARE @item_category_counter3 INT
DECLARE @loop_counter3 INT

SET  @loop_counter3 = ISNULL((SELECT COUNT(*) FROM @function_table),0)
-- Set the @loop_counter to the total number of rows in the memory table

SET @item_category_counter3 = 1

DECLARE @UpdateQueryHead NVARCHAR(4000)
DECLARE @UpdateQueryBody NVARCHAR(4000)
DECLARE @UpdateQueryFooter NVARCHAR(4000)
SET @UpdateQueryHead = 'INSERT INTO #PIVOT_FUNCTON_TABLE VALUES (' + (CAST
( @work_order  AS NVARCHAR(4000))) + ''
SET @UpdateQueryBody = ','
SET @UpdateQueryFooter = ')'

WHILE @loop_counter3 > 0 AND @item_category_counter3 <= @loop_counter3
BEGIN

--Create @UpdateQueryBody string
DECLARE @Value NVARCHAR(100)
SET @Value = Replace(
(SELECT RU.first_name + ' ' + RU.last_name
FROM wo_team WOT
INNER JOIN ref_wo_team rwt ON  WOT.dept_id = rwt.dept_id AND WOT.wo_team_id
= rwt.wo_team_id
INNER JOIN ref_user RU ON WOT.dept_id = RU.dept_id AND WOT.user_id = RU.
user_id
WHERE WOT.dept_id = @dept_id
AND work_order_id = @work_order
AND WOT.user_id <> ''
AND WOT.user_id <> 'NA'
AND WOT.user_id <> 'N/A'
AND wo_team_label_desc = (select [Function] FROM @function_table WHERE
primary_key = @item_category_counter3)),'''','')

SET @UpdateQueryBody = @UpdateQueryBody + (CASE WHEN @Value IS NULL THEN
'NULL' ELSE  '''' + @Value + '''' END) + ', '
SET @item_category_counter3 = @item_category_counter3 + 1

END

DECLARE @UpdateQuery NVARCHAR(4000)

--Remove trailing comma
SET @UpdateQueryBody = SUBSTRING ( @UpdateQueryBody ,1 , (len(
@UpdateQueryBody)-1))
SET @UpdateQuery = @UpdateQueryHead + @UpdateQueryBody + @UpdateQueryFooter

EXECUTE(@UpdateQuery)

SET @item_category_counterWO = @item_category_counterWO + 1
END