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
Values table
Id Value1 Value2
1 1 4
2 2 5
3 3 6
EDIT
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
The final 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 UNPIVOT
and PIVOT
.
EDIT2
Please see my answer,
I got it to work where we need only column to be processed ..
select value1 + 1 as Value1, value2 + 1 as value2
.