如果您不能使用动态存储过程中的数据创建临时表,为什么不直接在表上联接:
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)