N个配方的简单查询如下:
See working demo
declare @q varchar(max), @cols varchar(max)
set @cols
= STUFF((
SELECT distinct ',' +
QUOTENAME('Supplier '+
cast(row_number() over (partition by recipeid order by supplier ) as varchar(max))
)
FROM Ingredient
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @q=
'select
recipeid,'+ @cols +
' from
(
select
recipeid,
Supplier,
''Supplier ''+
cast(row_number() over (partition by recipeid order by supplier ) as varchar(max)) as r
from
Ingredient
)I
pivot
(
max(Supplier)
for r in ('+@cols+')
)piv'
exec(@q)
