Abdul Amin Khan

Abdul Amin Khan

  • 1.1k
  • 603
  • 21.3k

How to Merge highlighted Rows in one Row

Sep 28 2018 8:39 AM
 
 
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

Answers (2)