Link to home
Create AccountLog in
Avatar of ahf50
ahf50Flag for United States of America

asked on

Dynamic SQL Output as a Temp Table

The dynamic SQL code segment below takes a three field table and creates a pivot table...I would like to have the output stored as a temp table so that I can perform addtional operations on the data prior to creating output.  Everything I've tried has failed thus far.  


DECLARE @AsOfDate datetime
	SET @AsOfDate = (SELECT MAX(AsOfDate) FROM Global.FXHist (nolock))
	DECLARE @StartDate datetime
	--IF @StartDate = '1M'
	SET @StartDate = (SELECT (MAX(AsOfDate)-4000) FROM Global.FXHist (nolock))


	Select a.AsOfDate,a.RXRATE
			,case when b.Major = 'Y' THEN a.FXPAIR ELSE 'X' END [FXMAJOR]
	INTO #fxpair1
	From Global.FXHist a
	Left Join Global.FXPairCodes b
		On a.FXPAIR = b.FXPAIR
	Where AsOfDate > @StartDate


	Select AsOfDate,FXMAJOR,RXRATE
	INTO #fxpair2
	From #fxpair1
	Where FXMAJOR <> 'X'		


declare @sql nvarchar(max)
select @sql = coalesce(@sql+',','') + QuoteName(FXMAJOR)
from (select distinct FXMAJOR from #fxpair2) c
select @sql = '
Select AsOfDate [DT],' + @sql + '
From #fxpair2 p
PIVOT
      (
            max(rxrate) for FXMAJOR in (' + @sql + ')
      ) AS pvt
order by [DT] DESC'
exec (@sql)


DROP TABLE #fxpair1
DROP TABLE #fxpair2
end

Open in new window

Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image

Something like this perhaps:
DECLARE @AsOfDate datetime
SET @AsOfDate = (SELECT MAX(AsOfDate)
                 FROM   Global.FXHist (NOLOCK)
                )
DECLARE @StartDate datetime
	--IF @StartDate = '1M'
SET @StartDate = (SELECT    (MAX(AsOfDate) - 4000)
                  FROM      Global.FXHist (NOLOCK)
                 )


SELECT  a.AsOfDate,
        a.RXRATE,
        CASE WHEN b.Major = 'Y' THEN a.FXPAIR
             ELSE 'X'
        END [FXMAJOR]
INTO    #fxpair1
FROM    Global.FXHist a
        LEFT JOIN Global.FXPairCodes b ON a.FXPAIR = b.FXPAIR
WHERE   AsOfDate > @StartDate


SELECT  AsOfDate,
        FXMAJOR,
        RXRATE
INTO    #fxpair2
FROM    #fxpair1
WHERE   FXMAJOR <> 'X'		


DECLARE @sql nvarchar(max)
SELECT  @sql = COALESCE(@sql + ',', '') + QUOTENAME(FXMAJOR)
FROM    (SELECT DISTINCT
                FXMAJOR
         FROM   #fxpair2
        ) c

CREATE TABLE #YourTempTable(
				Col1 datatype,           --Change Column Name and datatype as appropriate
				Col2 datatype,
				Col3 datatype,
				...
				)

SELECT  @sql = '
Insert #YourTempTable(Col1, Col2, Col3, ...)
Select AsOfDate [DT],' + @sql + '
From #fxpair2 p
PIVOT
      (
            max(rxrate) for FXMAJOR in (' + @sql + ')
      ) AS pvt
order by [DT] DESC'
EXEC (@sql)


DROP TABLE #fxpair1
DROP TABLE #fxpair2

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Mohit Vijay
Mohit Vijay
Flag of India image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
>>you are trying that access that temp table outside of that dynamic query and that will not be accessible, it get destroyed when you executed that dynamic query.<<
If the temp table is created before the dynamic query is executed as in the solution I posted, then it will not be "destroyed".

Yes Agree that will not get destroyed, but because you have declared outside of dynamic query, it will not work in dynamic query.
>>but because you have declared outside of dynamic query, it will not work in dynamic query. <<
Really?  Perhaps you would like to double-check that first.
Here let me make it easy for you:
CREATE TABLE #MyTable(Value1 varchar(100))

INSERT #MyTable(Value1) VALUES ('This Item was created outside the Dynamic SQL')

EXEC ('INSERT #MyTable(Value1) VALUES (''This Item was created inside the Dynamic SQL'')')

SELECT Value1 FROM #MyTable

DROP TABLE #MyTable

Open in new window

Avatar of ahf50

ASKER

The problem w/ the 1st solution provided is the number of columns cannot be declared each time...the number of columns is being determined dynamically, and won't be the same every time.  Eventually this procedure will be called by an application that passes it the grouping to use as determined by the user.  
This is not considered best practices, but if you are bound and determined to do it using T-SQL, than IMHO you have no other choice, but do as suggested by VjSoft here http:#a34457289.  In other words do it entirely dynamically.

Good luck.
Avatar of ahf50

ASKER

I'm not tied to the dynamic SQL solution at all if the pivot can be coded another way.  The dynamic SQL solution is for the problem of having a large table w/ daily updates of about 300 currency pairs, three fields: AsOfDate, FXPAIR, RXRATE

Pivoting on AsOfDate in the first column, and across the FXPAIR(s) in scope, with the RXRATE for each FXPAIR/AsOfDate combination populating the resulting table is the initial problem, and the dynamic SQL code was a solution.  Though there might be a better way to do it, considering how difficult it would be to then store that result as a temp table.