نحوه چرخش داده ها با استفاده از Dynamic SQL در SQL Server

CREATE TABLE Goods([Store] VARCHAR(255), [Food] VARCHAR(255), [Amount] INT);

INSERT INTO Goods(Store, Food, Amount)
VALUES
('Store 1', 'potato', 50),
('Store 5', 'potato', 150),
('Store 12', 'potato', 300),
('Store 1', 'tomato', 220),
('Store 5', 'tomato', 180),
('Store 12', 'tomato', 60),
('Store 1', 'cucumber', 500),
('Store 5', 'cucumber', 10),
('Store 12', 'cucumber', 90);

GO

/*for turning rows into columns*/
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
    DROP TABLE #tmp 
CREATE TABLE #tmp(Store VARCHAR(255), Food VARCHAR(255))
GO
/*for final result*/
IF OBJECT_ID('tempdb..#result') IS NOT NULL
    DROP TABLE #result
GO

DECLARE @sql NVARCHAR(MAX) = '' /*dynamic string*/
        , @columns NVARCHAR(MAX) = STUFF(ISNULL((SELECT DISTINCT ', ['+ Food +'] INT'
                                                FROM Goods
                                                FOR XML PATH('')),''),1,2,'') /*column names for the final result table*/
        , @summary NVARCHAR(MAX) = STUFF(ISNULL((SELECT DISTINCT ', SUM(['+ Food +']) AS ['+ Food +']'
                                                FROM Goods
                                                FOR XML PATH('')),''),1,2,'') /*summing amount*/

SELECT @columns AS [columns], @summary AS [summary];

INSERT INTO #tmp 
SELECT DISTINCT Store, 
REPLACE(STUFF(ISNULL((SELECT DISTINCT ', 0 AS ['+ Food +']'
            FROM Goods rw
            FOR XML PATH('')),''),1,2,''),'0 AS ['+ Food +']', CAST(org.Amount AS VARCHAR) + ' AS ['+org.Food+']')
FROM Goods org;

SELECT * FROM #tmp;

--create final table according to number of columns from @columns and insert data from #tmp
SET @sql = N'create table #result(food varchar(255),'+@columns+'); '+CHAR(10)+
            'insert into #result '+CHAR(10);

SELECT @sql = @sql + N'select '''+Store+''' as Store, '+Food+' union all '+CHAR(10) FROM #tmp;

--remove last 'union all'
SET @sql = REVERSE(STUFF(REVERSE(@sql),1,11,''));

SET @sql = @sql + N'select food, '+@summary+' from #result group by food';

EXEC sp_executesql @sql;

SET @sql = @sql + N'create table final_result(food varchar(255),'+@columns+');'+CHAR(10)+
                    'insert into final_result'+CHAR(10)+
                    'select food, '+@summary+' from #result group by food';

EXEC sp_executesql @sql;

SELECT* FROM final_result;