I need to create a temp function, for use in a bigger query but I don't have create permissions on the database (except for #TEMP tables).
Is there any way that I could use a CTE or a #temp query for this purpose. Maybe I'm missing something really simple here.
Example (of what it could look like):-
with add1(x) as return x+1 select add1(v.Value1), add1(v.Value2) from Values v
Id Value1 Value2 1 1 4 2 2 5 3 3 6
As per Aaron Bertrand's answer I managed to get something working.
CREATE TABLE #myTempTable ( id int identity(1,1) primary key, amount int, col1 varchar(10), col2 varchar(4) ); -- quite a few more cols in my actual temp table, -- omitted to show the real issue INSERT #myTempTable(amount,col1, col2) VALUES(10,'a1', 'b1'),(15,'a2','b2'); ;WITH processed AS ( SELECT * FROM #myTempTable AS r UNPIVOT (Result FOR [Value] IN r.Amount) unp CROSS APPLY ( /******** COMPLEX FUNCTION HERE ********/ /**** Applies to output of unpivot *****/ SELECT unp.Result + 10 [Processed_amount] ) AS a --PIVOT (max(orig) FOR Value IN ( amount)) AS p2 ) select top 10 [Processed_amount], * from processed
PIVOT is messing up the results though.
I was wondering why that is required.
I am still trying to wrap my head around the various parts of
Please see my answer,
I got it to work where we need only column to be processed ..