You must Sign In to post a response.
  • Category: SQL Server

    Unable to group by on multiple column in Pivot sql Query

    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)
  • #763688
    Hi Rashed Bin Hares,

    Please mention Expectation output model


Sign In to post your comments