CODEWITHSUNDEEP
sqlsql-serveraggregate-functions
sm2mafaz
sm2mafaz

Reputation: 402

Joining a query result to a distinct dynamic query in SQL

I've been trying all day long to merge a simple query result of a single row to another dynamic query with a single row. To briefly explain what I've got is as follows:

I have a query as a result of a complex SELECT that returns a single row of two columns:

HistoryDate |TotalMarketValue
2014-08-31   1195687865.20

Then I have another dynamic query which returns a single row with dynamic number of columns, where I do not know the name nor number of columns that would be returned, which I execute something like this:

exec sp_executesql @query

So basically what I need to do is something like:

SELECT HistoryDate, SUM(TotalMarketValue) FROM MyComplexTable, exec sp_executesql @query

Obviously the above syntax is wrong as the exec part will not allow me to do a CROSS JOIN. I've tried various other ways like attempting to populate the dynamic query results to a temporary table, but then again it doesn't allow me to create a table without specifying the number of columns. I'm just missing that point.

Can somebody let me know how I could get the dynamic query as part of the merged query?

Upvotes: 2

Views: 1538

Answers (1)

Alex Hodge
Alex Hodge

Reputation: 76

You could use a table variable and insert the results of the query into it. Then select the data from the table variable and your select statement with a union.

DECLARE @query NVARCHAR(MAX)
SET @query = 'SELECT HistoryDate, SUM(TotalMarketValue)'

DECLARE @Results TABLE(cola DATETIME, colb DECIMAL(18,2))

INSERT INTO @Results
exec sp_executesql @query

SELECT * FROM @Results
UNION 
SELECT HistoryDate, SUM(TotalMarketValue)

Upvotes: 1

Related Questions