unable to group by on multiple column in Pivot sql Query [Resolved]

Posted by Rashedbin under Sql Server on 12/21/2015 | Points: 10 | Views : 376 | Status : [Member] | Replies : 3
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)




Responses

Posted by: Rajayadav on: 12/21/2015 [Member] Starter | Points: 50

Up
0
Down

Resolved
Hi, Please refer below url

http://blogs.msdn.com/b/kenobonn/archive/2009/03/22/pivot-on-two-or-more-fields-in-sql-server.aspx

Thanks
-Raja

Rashedbin, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Rajnilari2015 on: 12/21/2015 [Member] [Microsoft_MVP] [MVP] Platinum | Points: 25

Up
0
Down
@Rashedbin Sir, can u please post Sample DDL with expected Output ? It will be helpful.

Meanwhile you can read this article of mine ( http://www.dotnetfunda.com/articles/show/3158/import-xml-file-and-transform-into-relational-model-for-dynamic-column ) for dynamic pivoting and we believe that will help you to progress.

Thanks

--
Thanks & Regards,
RNA Team

Rashedbin, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Professionaluser on: 12/22/2015 [Member] [MVP] Bronze | Points: 25

Up
0
Down
refer below link for simulating your scenario... if u are not able to find exact solution, post us back the sample data for vw_ItemWiseSale view

http://stackoverflow.com/questions/14694691/sql-server-pivot-table-with-multiple-column-aggregates
https://dwbi1.wordpress.com/2010/08/11/using-pivot-for-2-value-columns/
http://sqlfiddle.com/#!3/4bd75/15

Rashedbin, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response