CODEWITHSUNDEEP
sqlsql-server
lukehawk
lukehawk

Reputation: 1493

How to combine EXEC statement results?

Here is an example table:

CREATE TABLE Example 
(
    LastName varchar(255),
    FirstName varchar(255),
    HomeAddress varchar(255),
    City varchar(255), 
    Gender varchar(255),
    Role_Type varchar(255),
);

INSERT INTO Example 
VALUES ('Murphy', 'James','123 Easy St', 'New York','Male', 'Manager'),
       ('Black', 'John','345 Youfarted St', 'Boston','Male', 'Employee'),
       ('Black', 'Amy','123 Simple St', 'Chicago','Female', 'Manager'),
       ('Simpson', 'Bill','123 Whofarted St', 'New York','Male', 'Employee'),
       ('Jones', 'Cindy','321 Foo St', 'Chicago','Female', 'Employee'),
       ('Black', 'John','275 Ipsum St', 'Boston','Male', 'Employee'),
       ('Murphy', 'Stephanie','983 Ifarted St', 'New York','Female', 'Employee');

These two exec statements are dynamic and do not use PIVOT, which I like. I would like to keep them the same (at the minimum functionally, but even better if actually). 

DECLARE @sql1 NVARCHAR(max) = '', @sql2 NVARCHAR(max) = ''

SELECT 
    @sql1 = @sql1 + ', COUNT(CASE WHEN city = ''' + City + ''' THEN 1 END) as ' + QUOTENAME(City) 
FROM
    Example
WHERE 
    City IS NOT NULL
GROUP BY 
    City

SET @sql1 = 'SELECT LastName' + @sql1 + ' FROM example GROUP BY LastName'

EXEC sp_executesql @sql1

SELECT 
    @sql2 = @sql2 + ', COUNT(CASE WHEN Role_Type = ''' + Role_Type + ''' THEN 1 END) AS ' + QUOTENAME(Role_Type) 
FROM
    Example
WHERE 
    Role_Type IS NOT NULL
GROUP BY 
    Role_Type

SET @sql2 = 'SELECT LastName' + @sql2 + ' FROM example GROUP BY LastName'

EXEC sp_executesql @sql2

These queries produce these two tables:

LastName    Boston  Chicago New York
-------------------------------------
Black            2        1        0
Jones            0        1        0
Murphy           0        0        2
Simpson          0        0        1

LastName    Employee    Manager
--------------------------------
Black              2          1
Jones              1          0
Murphy             1          1
Simpson            1          0

How do I combine these two queries? To produce one resulting table?? Something similar to what follows:

LastName    Boston  Chicago New York Employee Manager
------------------------------------------------------
Black            2        1        0        2       1
Jones            0        1        0        1       0
Murphy           0        0        2        1       1
Simpson          0        0        1        1       0

Upvotes: 1

Views: 1244

Answers (2)

Eli
Eli

Reputation: 2608

Some concatenation will work here - I tested this with your sample data with correct results: 

DECLARE @sql1 NVARCHAR(max) = '', @sql2 NVARCHAR(max) = '', @sql3 NVARCHAR(MAX) ='';

SELECT 
  @sql1 = @sql1 + ', COUNT(CASE WHEN city = ''' + City + ''' THEN 1 END) as ' + QUOTENAME(City) 
FROM
  Example
WHERE 
    City is not NULL
GROUP BY City;


SELECT 
  @sql2 = @sql2 + ', COUNT(CASE WHEN Role_Type = ''' + Role_Type + ''' THEN 1 END) as ' + QUOTENAME(Role_Type) 
FROM
  Example
WHERE 
    Role_Type is not NULL
GROUP BY Role_Type;


SET @sql3 =  'SELECT LastName' + @sql1 + @sql2 + ' FROM example GROUP BY LastName'

EXEC sp_executesql  @Sql3;

Upvotes: 1

Kashif Qureshi
Kashif Qureshi

Reputation: 1490

I think you need to use FULL outer join. This should work:

Declare @sql3 nvarchar(2000)

 set @sql3 = 'select * from (' + @sql1 + ') A FULL outer join  (' + @sql2 + ') b on a.LastName = b.LastName'
 exec sp_executesql @sql3

Examples: https://www.w3schools.com/sql/sql_join_full.asp

Upvotes: 0

Related Questions