I am working in a restaurant project.i have 4 table for sale module.
1.order master. 2.order details. 2. itemEntry 4. waiter_info
they require a report which will show each item sale quantity for each waiter.
MENU_NAME MENU_ID PRICE Total Foisal Kamal Sajib
Naan 2 10.00 8 6 2 0
Parata 1 10.00 10 6 4 0
Vaji 3 30.00 15 8 6 1
my query is below DECLARE @DynamicPivotQuery AS NVARCHAR(MAX) DECLARE @ColumnName AS NVARCHAR(MAX)
SELECT @ColumnName= ISNULL(@ColumnName + ',','') + QUOTENAME(Name) FROM (SELECT DISTINCT Name FROM Waiter_Info) AS Waiter print @ColumnName SET @DynamicPivotQuery = N'
SELECT * from vw_ItemWiseSale pivot ( sum(QUANTITY) for WAITER in ('+ @ColumnName +')) as pvt'
EXEC sp_executesql @DynamicPivotQuery
my output is
MENU_NAME MENU_ID PRICE Total Foisal Kamal Sajib
Naan 2 10.00 20 NULL 2 NULL
Naan 2 10.00 60 6 NULL NULL
Parata 1 10.00 40 NULL 4 NULL
Parata 1 10.00 60 6 NULL NULL
Vaji 3 30.00 30 NULL NULL 1
Vaji 3 30.00 180 NULL 6 NULL
Vaji 3 30.00 240 8 NULL NULL
please help me to get expected output item wise which mentioned in 3 line.(i,e each row for every item)