Hi RPA,
Check the below test query.
SQL
CREATE TABLE #FOOD(Id INT, NAME VARCHAR(10))
INSERT INTO #FOOD(Id, NAME)
SELECT 1,'apple' UNION ALL SELECT 2,'orange' UNION ALL SELECT 3,'tomato'
CREATE TABLE #PEOPLE(Id INT, NAME VARCHAR(10))
INSERT INTO #PEOPLE(Id, NAME)
SELECT 1,'Imomdod' UNION ALL SELECT 2,'Muhammad' UNION ALL SELECT 3,'Alisher'
CREATE TABLE #SALES(Id INT, IdPer INT, IDFOOD INT, price float)
INSERT INTO #SALES(Id, IdPer, IDFOOD, price)
SELECT 1,2,1, 3 UNION ALL SELECT 2,3,1, 5 UNION ALL SELECT 3,1,3, 11 UNION ALL
SELECT 4,3,2, 2 UNION ALL SELECT 5,2,1, 5 UNION ALL SELECT 6,1,2, 8 UNION ALL
SELECT 7,2,3, 4 UNION ALL SELECT 8,3,2, 10 UNION ALL SELECT 9,2,3, 15 UNION ALL
SELECT 10,1,2, 18 UNION ALL SELECT 11,3,3, 13 UNION ALL SELECT 12,1,3, 19
DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)
SELECT @ColumnName = ISNULL(@ColumnName + ',','')+ QUOTENAME(NAME) FROM (SELECT DISTINCT [NAME] FROM #FOOD) AS [NAME]
DECLARE @ColumnForSum AS NVARCHAR(MAX)
SELECT @ColumnForSum = REPLACE(@ColumnName,',','+')
SELECT @ColumnForSum = REPLACE(@ColumnForSum,'[','ISNULL([')
SELECT @ColumnForSum = REPLACE(@ColumnForSum,']','],0)')
SET @DynamicPivotQuery = 'SELECT ROW_NUMBER() OVER (ORDER BY(SELECT 0)) Id
,PersonName,'+@ColumnName+'
,SUM('+@ColumnForSum+') Summa
FROM (
SELECT SUM(s.Price) Price,p.NAME as PersonName,f.NAME as FoodName
from #SALES s
INNER JOIN #PEOPLE p ON s.IdPer = p.Id
INNER JOIN #FOOD f ON s.IDFOOD = f.Id
GROUP BY p.Name,f.Name)t
pivot
(
MAX([Price]) FOR FoodName IN ('+@ColumnName+')
) piv GROUP BY PersonName,'+@ColumnName+''
EXEC (@DynamicPivotQuery)
DROP TABLE #SALES
DROP TABLE #FOOD
DROP TABLE #PEOPLE
Output
| Id |
PersonName |
apple |
orange |
tomato |
Summa |
| 1 |
Alisher |
5 |
12 |
13 |
30 |
| 2 |
Imomdod |
NULL |
26 |
30 |
56 |
| 3 |
Muhammad |
8 |
NULL |
19 |
27 |
