query optimization

Question

i want the result in single line but am getting in multiple line

ejaz mirza · Answer

i didn't get your point if you can solve below is the query Select * from ( select a.StockLocationName, a.Grade+'a' As Grade_a, a.Grade+'b' As Grade_b, a.Grade+'c' As Grade_c, a.Grade+'d' As Grade_d, sum(CASE WHEN(datediff(day, DocDate, '04-01-2018 00:00:00') > 0) THEN(a.Q1 - a.Q2 + a.Q3) / 20 ELSE 0 END) as OpStkMTS, sum(CASE WHEN(datediff(day, DocDate, '04-01-2018 00:00:00') = 0) THEN(a.Q1 +a.Q3) / 20 ELSE 0 END) as ReceiptStkMTS, sum(CASE WHEN(datediff(day, DocDate, '04-01-2018 00:00:00') = 0) THEN a.Q2 / 20 ELSE 0 END) as DispatchStkMTS, sum(CASE WHEN(datediff(day, DocDate, '04-01-2018 00:00:00') >= 0) THEN(a.Q1 - a.Q2 + a.Q3) / 20 ELSE 0 END) as ClosingStkMTS from( select StockLocationName, Grade,InvoiceDt as DocDate, Sum(CONVERT(float,GoodsReceivedBy))*20 as Q1, 0 AS Q2, 0 as Q3, 0 as Q0 from StockReceiptsNow where StockLocationName = 'CRWC' group by StockLocationName, Grade, InvoiceDt UNION ALL Select StockLocationName, Grade, DocDate, 0 AS Q1, Sum(QtyBags) as Q2, 0 as Q3, 0 as Q0 from Dispatches where StockLocationName = 'CRWC' Group by StockLocationName, Grade, DocDate UNION ALL Select StockLocationName, Grade, DocDate, 0 AS Q1, 0 as Q2, Sum(QtyBags) as Q3, 0 as Q0 from GodownTransfer where StockLocationName ='CRWC' Group by StockLocationName, Grade, DocDate) a group by a.StockLocationName, a.Grade) As b pivot ( Sum(OpStkMTS) for Grade_a in([OPC 43-Gr NRSa],[OPC 53-Gr MRPa],[OPC 53-Gr NRSa],[PPC Gr MRPa],[PPC Gr-NRSa]) ) as pivotTable1 pivot ( Sum(ReceiptStkMTS) for Grade_b in([OPC 43-Gr NRSb],[OPC 53-Gr MRPb],[OPC 53-Gr NRSb],[PPC Gr MRPb],[PPC Gr-NRSb]) ) as pivotTable1 pivot ( Sum(DispatchStkMTS) for Grade_c in([OPC 43-Gr NRSc],[OPC 53-Gr MRPc],[OPC 53-Gr NRSc],[PPC Gr MRPc],[PPC Gr-NRSc]) ) as pivotTable1 pivot ( Sum(ClosingStkMTS) for Grade_d in([OPC 43-Gr NRSd],[OPC 53-Gr MRPd],[OPC 53-Gr NRSd],[PPC Gr MRPd],[PPC Gr-NRSd]) ) as pivotTable1

Ruchi Sharavat · Answer

Remove extra column from inner select statement OR make sure those fields have same values for all of the rows for which you want to have single row output. Just select "Grade_a" and "opStkMTS" from inner statement to test it and than add other columns one by one.