how to join dynamic SQL results with another query
in [Programming SQL]
|
Prev: DB2 on SS
Next: SSRS doesnt like Guid param datatypes
From: new DBA in '09 on 8 Jun 2010 19:57 Hi Everybody, I'm using dynamic SQL to execute a pivot query for a sales report since the column names will change by month. The very last statement is "EXECUTE sp_executesql @query", where @query is my pivot query string. How do I join these results with another query so as to get one resulting dataset? For example, here's my "other query" results: SELECT 1 AS [CustId], 'ABC Corp.' AS [CustName] UNION ALL SELECT 2 AS [CustId], 'Microsoft' And here's my pivot query results: SELECT 1 AS [CustNum], 35.99 AS [Jan_Sales], 104.52 AS [Feb_Sales] UNION ALL SELECT 2, 14.79, 25.99 Ultimately, I want the following results: SELECT 1 AS [CustId], 'ABC Corp.' AS [CustName], 35.99 AS [Jan_Sales], 104.52 AS [Feb_Sales] UNION ALL SELECT 2, 'Microsoft', 14.79, 25.99 I suppose this has nothing to do with my pivot query. I could execute any query using dynamic sql, but I'm wondering how to join the results with another query. Any advice you could give would be greatly appreciated. We don't have SSAS set up yet and I'm not qualified to run it yet, anyway. Thanks, Eric
From: Plamen Ratchev on 8 Jun 2010 21:19 You could use OPENQUERY to execute the dynamic SQL and join the result set. Good to read the following first: http://www.sommarskog.se/share_data.html#OPENQUERY Alternative is to use INSERT...EXEC to store the result set to table and join the table. -- Plamen Ratchev http://www.SQLStudio.com
From: sam on 9 Jun 2010 01:31 On Jun 9, 4:57 am, "new DBA in '09" <ericbra...(a)gmail.com> wrote: > Hi Everybody, > > I'm using dynamic SQL to execute a pivot query for a sales report > since the column names will change by month. The very last statement > is "EXECUTE sp_executesql @query", where @query is my pivot query > string. How do I join these results with another query so as to get > one resulting dataset? > > For example, here's my "other query" results: > > SELECT 1 AS [CustId], 'ABC Corp.' AS [CustName] > UNION ALL > SELECT 2 AS [CustId], 'Microsoft' > > And here's my pivot query results: > > SELECT 1 AS [CustNum], 35.99 AS [Jan_Sales], 104.52 AS [Feb_Sales] > UNION ALL > SELECT 2, 14.79, 25.99 > > Ultimately, I want the following results: > > SELECT 1 AS [CustId], 'ABC Corp.' AS [CustName], 35.99 AS [Jan_Sales], > 104.52 AS [Feb_Sales] > UNION ALL > SELECT 2, 'Microsoft', 14.79, 25.99 > > I suppose this has nothing to do with my pivot query. I could execute > any query using dynamic sql, but I'm wondering how to join the results > with another query. > > Any advice you could give would be greatly appreciated. We don't have > SSAS set up yet and I'm not qualified to run it yet, anyway. > > Thanks, > Eric SELECT Test1.*,Test2.Jan_Sales,Test2.Feb_Sales FROM (SELECT 1 AS [CustId], 'ABC Corp.' AS [CustName] UNION ALL SELECT 2 AS [CustId], 'Microsoft' ) AS Test1 LEFT OUTER JOIN --And here's my pivot query results: (SELECT 1 AS [CustNum], 35.99 AS [Jan_Sales], 104.52 AS [Feb_Sales] UNION ALL SELECT 2, 14.79, 25.99 ) AS Test2 ON Test1.CustId = Test2.CustNum
From: Uri Dimant on 9 Jun 2010 04:33 WITH cte1 AS ( SELECT 1 AS [CustId], 'ABC Corp.' AS [CustName] UNION ALL SELECT 2 AS [CustId], 'Microsoft' ),cte2 AS ( SELECT 1 AS [CustNum], 35.99 AS [Jan_Sales], 104.52 AS [Feb_Sales] UNION ALL SELECT 2, 14.79, 25.99 ) SELECT * FROM cte1 JOIN cte2 ON cte1.[CustId]=cte2.[CustNum] "new DBA in '09" <ericbragas(a)gmail.com> wrote in message news:237d38c1-27c0-4ec7-a7d0-b23ddf463de0(a)11g2000prv.googlegroups.com... > Hi Everybody, > > I'm using dynamic SQL to execute a pivot query for a sales report > since the column names will change by month. The very last statement > is "EXECUTE sp_executesql @query", where @query is my pivot query > string. How do I join these results with another query so as to get > one resulting dataset? > > For example, here's my "other query" results: > > SELECT 1 AS [CustId], 'ABC Corp.' AS [CustName] > UNION ALL > SELECT 2 AS [CustId], 'Microsoft' > > And here's my pivot query results: > > SELECT 1 AS [CustNum], 35.99 AS [Jan_Sales], 104.52 AS [Feb_Sales] > UNION ALL > SELECT 2, 14.79, 25.99 > > Ultimately, I want the following results: > > SELECT 1 AS [CustId], 'ABC Corp.' AS [CustName], 35.99 AS [Jan_Sales], > 104.52 AS [Feb_Sales] > UNION ALL > SELECT 2, 'Microsoft', 14.79, 25.99 > > I suppose this has nothing to do with my pivot query. I could execute > any query using dynamic sql, but I'm wondering how to join the results > with another query. > > Any advice you could give would be greatly appreciated. We don't have > SSAS set up yet and I'm not qualified to run it yet, anyway. > > Thanks, > Eric
From: Erland Sommarskog on 9 Jun 2010 17:59
new DBA in '09 (ericbragas(a)gmail.com) writes: > I'm using dynamic SQL to execute a pivot query for a sales report > since the column names will change by month. The very last statement > is "EXECUTE sp_executesql @query", where @query is my pivot query > string. How do I join these results with another query so as to get > one resulting dataset? > > For example, here's my "other query" results: > > SELECT 1 AS [CustId], 'ABC Corp.' AS [CustName] > UNION ALL > SELECT 2 AS [CustId], 'Microsoft' > > And here's my pivot query results: > > SELECT 1 AS [CustNum], 35.99 AS [Jan_Sales], 104.52 AS [Feb_Sales] > UNION ALL > SELECT 2, 14.79, 25.99 > > Ultimately, I want the following results: > > SELECT 1 AS [CustId], 'ABC Corp.' AS [CustName], 35.99 AS [Jan_Sales], > 104.52 AS [Feb_Sales] > UNION ALL > SELECT 2, 'Microsoft', 14.79, 25.99 > > I suppose this has nothing to do with my pivot query. I could execute > any query using dynamic sql, but I'm wondering how to join the results > with another query. That do would have to be a venture with dynamic SQL, since you don't know the structure of the result set of the pivot query. I think the best option is to include the join when building the pivot query from the start. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx |