How to Merge highlighted Rows in one Row

 

 

My Query is----

 

SELECT Date,Bill_No,Restaurant_name,Discounts,[GST 0%],[0],[GST 5%],[5],[GST 12%],[12],[GST 18%],[18],[GST 28%],[28],ServiceCharge_Percent,ServiceCharge_Amount,VATOnServiceCharge_Amount,Othercharges,ChangediscVal,NetSaleBeforeDiscount,NetSaleAfterDiscount,RoundOff,BillTotalCard_Amount,BillCash_Amount,NetTotal,Restaurant_Code,Terminal_Code,Bill_no1

FROM

(select ih.Date,

ih.Bill_No,ih.Bill_no1,

rest.Restaurant_name,

Round(ih.SubDiscount_Amount,2) as Discounts,

Round(ss.Service_charge,2) as ServiceCharge_Percent,

Round(ih.ServiceCharge_Amount,2) as ServiceCharge_Amount,

Round(ih.VATOnServiceCharge_Amount,2) as VATOnServiceCharge_Amount,

Round((isnull(ih.othercharge1,0)+isnull(ih.othercharge2,0)+isnull(ih.othercharge3,0)),2)as Othercharges,

Round(ih.ChangediscVal,2) as ChangediscVal,

Round(ih.SubTotal,2) as NetSaleBeforeDiscount,

Round((ih.SubTotal-ih.SubDiscount_Amount),2) as NetSaleAfterDiscount,

Round(isnull(ih.RoundOff,0),2) as RoundOff,

Round(ih.BillTotalCard_Amount,2) as BillTotalCard_Amount,

Round(ih.BillCash_Amount,2) as BillCash_Amount,

Round(ih.NetTotal,2) as NetTotal,

ih.Restaurant_Code,

ih.Terminal_Code,

Sum(il.Item_Price) as Price,

il.Vat_percent AS Tax_Percent,

tr.Tax_name AS Tax_Percent1,

Sum(il.Vat_amount) as Tax_amount

from INVHEAD ih

inner join InvLine il on il.Bill_no=ih.Bill_no

inner join TaxRates tr on tr.Tax_Rate=il.VAT_Percent

inner join Restaurants rest on ih.Restaurant_Code=rest.Restaurant_Code

inner join SystemSetup ss on rest.Restaurant_Code=ss.Restaurant_Code

where ih.Date BETWEEN '01 Aug 2018' AND '31 Aug 2018'

and ih.Restaurant_Code='1'

Group By il.Vat_percent,ih.Date,ih.Bill_No,rest.Restaurant_name,ih.SubDiscount_Amount,ss.Service_charge,ih.ServiceCharge_Amount,ih.VATOnServiceCharge_Amount,ih.othercharge1,ih.othercharge2,ih.othercharge3,ih.ChangediscVal,ih.SubTotal,ih.RoundOff,ih.BillTotalCard_Amount,ih.BillCash_Amount,ih.NetTotal,ih.Restaurant_Code,ih.Terminal_Code,

il.Vat_percent,ih.Bill_No1,tr.Tax_name)Tab1

PIVOT

(Sum(Tax_amount) FOR [Tax_Percent] IN ([0],[5],[12],[18],[28])) AS Tab2

PIVOT

(Sum(Price) FOR [Tax_Percent1] IN ([GST 0%],[GST 5%],[GST 12%],[GST 18%],[GST 28%])) AS

Tab3

ORDER BY Date,Bill_No1