Join n result stets horizontal

Question

I know that there are many questions on this topic, but no one seem to works for my problem

Shortly, I want to merge horizontal an unknown number of result sets as in the following example

Result 1:

Name |sum1 |sum2
________________
name1| 0.5 |0.1
name2| 0.6 |0.2

Result 2:

Name |sum1 |sum2
________________
name1| 1.5 |0.7
name2| 1.6 |0.9


.
.
.

Result n:

Name |sum1 |sum2
________________
name1| 7.5 |9.7
name2| 8.6 |5.9

Finally :

Name |sum1 |sum2| sum1 | sum2|.......| sum1|sum2
________________________________________
name1| 0.5 |0.1 | 1.5  | 0.7 |.......| 7.5 |9.7
name2| 0.6 |0.2 | 1.6  | 0.9 |.......| 8.6 |5.9

The column "Name" is exactly the same in all of the result.

Think you guys could help out?

Without building dynamically at run time I don't think this is possible — Chris O'Connor, Jul 25 '18 at 20:49
`unknown number of result sets` does that mean, you have unknown number of tables? — Vinit, Jul 25 '18 at 20:49
What RDBMS are you using? MySQL, SQL-Server, Oracle, SQLite, PostGres? — Barmar, Jul 25 '18 at 21:04
@D-Shih Each result set is returned by a selection on a specified time interval (unknown number of interval) and for each name I add the sum1 and sum2 columns by joining another table that contains a list of transactions — Costin Adrian, Jul 25 '18 at 21:18

score 0 · Answer 1 · answered Jul 25 '18 at 21:21

You would use join:

select r1.name, r1.sum1, r1.sum2, r2.sum1, r2.sum2, r3.sum1, r3.sum2
from result1 r1 join
     result2 r2
     on r1.name = r2.name join
     results r3
     on r1.name = r3.name

You would need to continue this for each result set.

Now, having an unknown number of result sets makes this more complicated. That simply requires generating a dynamic SQL statement, based on the same logic.

Edward · Answer 2 · 2018-07-25T22:06:39.050

Quick stored procedure to dynamically build your query string and execute it. You can control the execute with the first input @in_run_query.

CREATE PROCEDURE dynamic_sql_query 
   @in_run_query INT 
,  @in_count_results_sets INT 
AS 
BEGIN 

IF @in_count_results_sets IS NULL 
 EXIT 

IF TRY_CONVERT(INT,@in_count_results_sets) IS NULL 
 EXIT 

 IF @in_count_results_sets < 2 
    BEGIN 
        SELECT 'Counter must be between 2 and 100'
        EXIT 
    END

IF @in_count_results_sets > 100 
    BEGIN 
        SELECT 'Build a better database'
        EXIT 
    END     

DECLARE @sql_string NVARCHAR(MAX) , @counter INT = 2 

SET @select = 'SELECT  r1.name, r1.sum1, r1.sum2'
SET @from = 'FROM result1 AS r1'

LOOP:

SET @prefix = 'r' + CAST(@counter AS String) 
SET @full_name = 'result' + CAST(@counter AS String) 
-- select 
SET @select = @select + ', ' + @prefix + '.name, ' + @prefix + '.sum1, ' + @prefix + '.sum2'
-- from 
SET @from = @from + '  join ' + @full_name + ' AS ' + @prefix + ' on r1.name = ' + @prefix + '.name'

IF @counter = @in_count_results_sets
     GOTO AppendStrings 

@counter = @counter + 1 

GOTO LOOP    


AppendStrings: 

SET @sql_string = @select + ' ' + @from + ';'

IF @in_run_query <> 1
    BEGIN 
        SELECT @sql_string 
        EXIT 
    END
 EXECUTE sp_executesql  @sql_string 
END

Join n result stets horizontal

2 Answers2