hendridm hendridm - 1 year ago 31
SQL Question

SQL Server 2008: Joining results of STORED PROCEDURE (dynamic columns) with results of a SELECT statement

I have results that are generated by this stored procedure.

I want to join these results with data in another table. I've seen various examples of doing this be creating a temporary table and inserting into it, however, this would not be ideal as the stored procedure returns many dynamic columns which are subject to change. Is there a way to join them dynamically?

Example scenario:

Stored Procedure Returns This:



EXEC uspGetProductCategories

products_id | products_model | Leather Seats | Heated Seats | Tapedeck | Heater | Hybrid | Sunroof | Cruise Control
===================================================================================================================
100 | Saturn Vue | N | N | Y | N | N | N | N
200 | Toyota Pruis | Y | N | N | Y | Y | N | N
300 | Ford Focus | N | N | N | Y | N | N | Y


I want to JOIN it with a SQL query that generates something like:



SELECT * FROM Products_Detail

products_id | manufacturer | purchaser | pay_type
=================================================
100 | GM | GREG | P
200 | TOYT | SAM | P
300 | FORD | GREG | L


In other words...



Is there a painless way to accomplish this? Here is some psedo code of what I'd like to achieve (though I'm aware that this doesn't work):

SELECT pd.*, sp.* FROM Products_Detail pd
LEFT JOIN uspGetProductCategories sp ON pd.product_id = sp.product_id


Again, I know you can't do this, but hopefully it describes the logic I'm looking for.

Example Desired Output



products_id | manufacturer | purchaser | pay_type | products_model | Leather Seats | Heated Seats | Tapedeck | Heater | Hybrid | Sunroof | Cruise Control
=========================================================================================================================================================
100 | GM | GREG | P | Saturn Vue | N | N | Y | N | N | N | N
200 | TOYT | SAM | P | Toyota Pruis | Y | N | N | Y | Y | N | N
300 | FORD | GREG | L | Ford Focus | N | N | N | Y | N | N | Y

Answer Source

If you cannot create a temp table with the data from the dynamic stored procedure, why not just join on the table directly:

DECLARE @cols AS NVARCHAR(MAX),
    @colsNull AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(categories_name) 
                    from Categories
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

select @colsNull = STUFF((SELECT ',IsNull(' + QUOTENAME(categories_name)+', ''N'')'+' as '+QUOTENAME(categories_name) 
                    from Categories
                    group by categories_name, categories_id
                    order by categories_id
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = '
            select *
            from Products_Detail pd
            left join
            (
              SELECT products_id,
                    products_model,' + @colsNull + ' from 
               (
                  select p.products_id,
                    p.products_model,
                    c.categories_name,
                    ''Y'' flag
                  from products p
                  left join Products_Categories pc
                    on p.products_id = pc.products_id
                  left join Categories c
                    on pc.categories_id = c.categories_id
              ) x
              pivot 
              (
                  max(flag)
                  for categories_name in (' + @cols + ')
              ) p 
            ) p 
              on pd.products_id = p.products_id'

execute(@query)

See SQL Fiddle with Demo