Join results in SQL Server

Question

Join results in SQL Server

Navigation

#1 by (1 votes)

2

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
BEGIN
    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;
END
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  
+-------------+

sql sql-server join select

asked by Jonathan Castro 26.12.2017 в 21:40

source

1 answer

Show available rooms according to two dates entered (ranges) Browse div with class and show only the selected item

score 1 · Accepted 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:

DECLARE @sql NVARCHAR(MAX) = N'', @cols NVARCHAR(MAX) = N'';

SELECT @cols += STUFF((SELECT ',' + QUOTENAME(mod_Id)
                       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;