0
DECLARE @sql1 NVARCHAR(MAX),
    @sql2 NVARCHAR(MAX);

Let's create a temp table named #Table1 to put output from first dynamic query

CREATE TABLE #Table1   
(
    [CustomerID] varchar(20) NULL,
    [CustomerName] varchar(20) NULL,
    [DColumn01] char(1) NULL,
    [DColumn02] char(1) NULL,
    [DColumn03] char(1) NULL,
    [DColumn04] char(1) NULL
    -- .....           
    -- .....           
    [DColumn10] char(1) NULL
);

Let's create a temp table named #Table2 to put output from second dynamic query

    CREATE TABLE #Table2   
(
    [CustomerID] varchar(20) NULL,
    [CustomerName] varchar(20) NULL,
    [PColumn01] char(1) NULL,
    [PColumn02] char(1) NULL,
    [PColumn03] char(1) NULL,
    [PColumn04] char(1) NULL
    -- .....           
    -- .....           
    [PColumn10] char(1) NULL
    -- .....           
    -- .....           
    [PColumn20] char(1) NULL
);

Based on some condition first dynamic query select output look like below select query from #Table1:

SET @sql1 = N'SELECT [CustomerID], [CustomerName], [DColumn01], [DColumn02], [DColumn03], [DColumn04], [DColumn10] FROM #Table1';  

EXECUTE sp_executesql @sql1;

Here select column can vary based on other dynamic condition. Here column name can vary from "01" to "10" for DColumn like [DColumn09]

Based on some condition second dynamic query select output look like below select query from #Table2 

SET @sql2 = N'SELECT [CustomerID], [CustomerName], [PColumn01], [PColumn02], [PColumn03], [PColumn04], [PColumn10], [PColumn20] FROM #Table2';

EXECUTE sp_executesql @sql2;

Here select column can vary based on other dynamic condition. Here column name can vary from "01" to "20" for PColumn like [PColumn15].

Now I need to join both dynamic query based on [CustomerID].

[CustomerID], [CustomerName], [DColumn01], [DColumn02], [DColumn03], [DColumn04], ....., [DColumn10], [PColumn01], [PColumn02], [PColumn03], [PColumn04], ....., [PColumn10], ....., [PColumn20]

AS the select query can vary based on column name, so I am not able to insert into any temp table.

My thought:

SET @sql1 = N'SELECT [CustomerID], [CustomerName], [DColumn01], [DColumn02], [DColumn03], [DColumn04], [DColumn10]
              INTO #abc 
              FROM #Table1;

              SELECT [CustomerID] AS [PCustomerID], [CustomerName] AS [PCustomerName], [PColumn01], [PColumn02], [PColumn03], [PColumn04], [PColumn10],  [PColumn20]
              INTO #xyz 
              FROM #Table2;

              SELECT t1.*, t2.*
              FROM #Table1 AS t1 
              INNER JOIN #Table2 AS t2 ON t1.[CustomerID] = t2.[CustomerID]';

EXECUTE sp_executesql @sql1;

DROP TABLE #Table1;
DROP TABLE #Table2;

How can i join that two dynamic SQL result set and get output where column name is not predefined? I tried to do one way, but not sure is there any better way to do that

Saaif
  • 41
  • 11
  • What is your question here..? You don't ask one. – Thom A Mar 11 '18 at 19:16
  • @larnu How can i join that two dynamic sql result set and get output where column name is not predefined? I tried to do one way, but not sure is there any better way to do that – Saaif Mar 11 '18 at 19:18
  • You cant `JOIN` resultsets. You need to have the data in an object. If the column names aren't defined, how do you know what colums(s) have a relationship between the 2 datasets? If you can't define that relationship, you can't link the data. You have a `JOIN` in your final statement, what's wrong with it? We don't have access to your data or knowledge of why your statement is are dynamic. Why is it going into a temporary table; why not use the original source table? This seems like an xy question; the problem isn't how to `JOIN` your datasets, it's probably that you have made them dynamic – Thom A Mar 11 '18 at 19:29
  • @Larnu Ok, let's forget temporary table. My code are look like my real problem where I have two dynamic query and select columns comes from a PIVOT query output and as I said column is not predefined except join column ([CustomerID]). So now my question is how i will get my expected output. I tired to solved it as I mention. So is there any better way to do that? – Saaif Mar 11 '18 at 20:11
  • I think you need to provide the raw ingredient tables that are fed into the pivot first. Perhaps there is another way that bypasses the pivot completely, or does the pivot after the join? BTW, the word is "vary", not "very" – Alan Mar 11 '18 at 23:37
  • Maybe these answers provide some ideas: https://stackoverflow.com/q/18657214/1187211 – Razvan Socol Mar 13 '18 at 05:46

0 Answers0