Join results in SQL Server


I would like to know how I can do to join the results of several selects in the same temporary table if possible, or if there is another way to do it.

This is the code that I have at the moment:

Declare @mod as int;
Declare modelos cursor for select mod_Id from dbo.MODELS;
Open modelos;
fetch next from modelos into @mod;
while @@FETCH_STATUS = 0
    select b.brn_Name,count(@mod) from dbo.BRANCHES b, dbo.BrnHasMac bm, dbo.MACHINES m where b.brn_Id = bm.brn_Id and m.mac_Id = bm.mac_Id and m.mod_Id = @mod group by (b.brn_Name);
    FETCH NEXT FROM modelos into @mod;
Close modelos;
DeAllocate modelos

I am currently getting the results as follows:

Branch1 30  
Branch2 40  
Branch1 10  
Branch2 5  

But I would like to get them together as follows:

Branch1 30 10  
Branch2 40 5  
asked by Jonathan Castro 26.12.2017 в 21:40

1 answer


There are several problems with your current query. First of all, SQL works in sets, so that that way of writing the code should be preferable to using cursors (for efficiency). Now, there are times where a cursor is the best, but this is not the case in your code.

On the other hand, please try to use explicit joins. Implicit joins like the ones you're using are deprecated more than 20 years ago.

Finally, you're going to have to use a% dynamic% co as you intuited @sstan. The code should look something like:


                       FROM dbo.Models
                       GROUP BY Modelos
                       FOR XML PATH('')), 1, 1, '');

SET @sql = N'SELECT *
  FROM (SELECT b.brn_Name, m.mod_Id
        FROM dbo.BRANCHES b
        INNER JOIN dbo.BrnHasMac bm
            ON b.brn_Id = bm.brn_Id
        INNER JOIN dbo.MACHINES m 
            ON m.mac_Id = bm.mac_Id
  ) AS d
  PIVOT (COUNT([mod_Id]) FOR [mod_Id] IN (' + @cols + ')) AS p;';

EXEC sp_executesql @sql;
answered by 26.12.2017 / 21:59