This returns multiple rows and I would like to have the data as columns-CodePudding

This returns multiple rows and I would like to have the data as columns. Is there a way to combine these queries to get a single table result?

/* Loading for a day */
DECLARE @date1 AS date = '2022-05-07'

SELECT 
    SUM(EndTotalizerGST-InitialTotalizerGST) AS EDGB7
FROM
    APSisak.Loading.BatchReport
WHERE 
    CAST(StartDateTime AS DATE) LIKE @date1 
    AND MainMaterialName LIKE 'EDGB7'
 
SELECT 
    SUM(EndTotalizerGST-InitialTotalizerGST) AS BMB95
FROM 
    APSisak.Loading.BatchReport
WHERE 
    CAST(StartDateTime AS DATE) LIKE @date1 
    AND MainMaterialName LIKE 'BMB95'
 
SELECT 
    SUM(EndTotalizerGST-InitialTotalizerGST) AS BMB100
FROM 
    APSisak.Loading.BatchReport
WHERE 
    CAST(StartDateTime AS DATE) LIKE @date1 
    AND MainMaterialName LIKE 'BMB100'

CodePudding user response：

Use conditional aggregation:

select 
    Sum(case when MainMaterialName like 'EDGB7' then EndTotalizerGST-InitialTotalizerGST else 0 end) as EDGB7,
    Sum(case when MainMaterialName like 'BMB95' then EndTotalizerGST-InitialTotalizerGST else 0 end) as BMB95,
    Sum(case when MainMaterialName like 'BMB100' then EndTotalizerGST-InitialTotalizerGST else 0 end) as BMB100
from APSisak.Loading.BatchReport
where Cast(StartDateTime as date) like @date1 
  and MainMaterialName in ('EDGB7', 'BMB95', 'BMB100');