I have this table:

I want it to look like:

I kind of have it going the way I want with this query

select EUc.ID, EUc.[Year], EUc.[Month], EUc.Electricity_Use, EUc.Electricity_Cost, EUc.Natural_Gas_Use, EUc.Natural_Gas_Cost, EUc.Water_Use, EUc.Water_Cost,
EUc.Diesel_Use, EUc.Diesel_Cost, EUc.Gasoline_Use, EUc.Gasoline_Cost, EUc.Propane_Use, EUc.Propane_Cost
from
(select distinct EUb.ID, EUb.[Year], EUb.[Month],
    max(case when seq = 3 then [Use] end) Electricity_Use,
    max(case when seq = 3 then [Cost] end) Electricity_Cost,
    max(case when seq = 7 then [Use] end) Natural_Gas_Use,
    max(case when seq = 7 then [Cost] end) Natural_Gas_Cost,
    max(case when seq = 14 then [Use] end) Water_Use,
    max(case when seq = 14 then [Cost] end) Water_Cost,
    max(case when seq = 1 then [Use] end) Diesel_Use,
    max(case when seq = 1 then [Cost] end) Diesel_Cost,
    max(case when seq = 4 then [Use] end) Gasoline_Use,
    max(case when seq = 4 then [Cost] end) Gasoline_Cost,
    max(case when seq = 10 then [Use] end) Propane_Use,
    max(case when seq = 10 then [Cost] end) Propane_Cost
from 
    (
    Select EUa.ID, EUa.[Year], EUa.[Month], EUa.[Metric_Type], EUa.[Cost], EUa.[Use], 
    DENSE_RANK() Over(Order by Metric_Type asc) seq from dbo.Usage as EUa
        ) EUb
GROUP BY ID, [Year], [Month], Metric_Type, [Use], Cost, EUb.seq) as EUc
ORDER BY EUc.ID, EUc.[Year] desc, DATEPART(mm, CAST(EUc.[Month]  '1900' AS DATETIME)) asc

But my select distinct is not solving my need of having only one row per ID/Month/Year combination due to the nature of the original table.

I kind of understand -why- it is happening: I have two April rows in the original table, one with NULL and one with data, so the Distinct is making it two separate rows. What I can't figure out is how to make the table I want with the table I have.

CodePudding user response：

Consider conditioning directly on the type field:

SELECT [ID], [Year], [Month], 
    SUM(CASE WHEN type = 'Electricity' THEN [Use] END) AS [Electricity Use], 
    SUM(CASE WHEN type = 'Electricity' THEN [Cost] END) AS [Electricity Cost], 
    SUM(CASE WHEN type = 'Natural Gas' THEN [Use] END) AS [Natural Gas Use], 
    SUM(CASE WHEN type = 'Natural Gas' THEN [Cost] END) AS [Natural Gas Cost], 
    SUM(CASE WHEN type = 'Diesel' THEN [Use] END) AS [Diesel Use], 
    SUM(CASE WHEN type = 'Diesel' THEN [Cost] END) AS [Diesel Cost]
FROM dbo.Usage
GROUP BY [ID], [Year], [Month]
ORDER BY [ID], [Year], [Month]