title: Using a Case and Select Distinct to Switch Columns and Rows is Returning more Rows than Desired
body: <p>I have this table:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>ID</th>
  <th>Month</th>
  <th>Year</th>
  <th>Use</th>
  <th>Cost</th>
  <th>Type</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>12422</td>
  <td>January</td>
  <td>2022</td>
  <td>NULL</td>
  <td>NULL</td>
  <td>ELectricity</td>
  </tr>
  <tr>
  <td>12422</td>
  <td>February</td>
  <td>2022</td>
  <td>NULL</td>
  <td>NULL</td>
  <td>ELectricity</td>
  </tr>
  <tr>
  <td>12422</td>
  <td>March</td>
  <td>2022</td>
  <td>NULL</td>
  <td>NULL</td>
  <td>ELectricity</td>
  </tr>
  <tr>
  <td>12422</td>
  <td>April</td>
  <td>2022</td>
  <td>13</td>
  <td>44</td>
  <td>ELectricity</td>
  </tr>
  <tr>
  <td>12422</td>
  <td>May</td>
  <td>2022</td>
  <td>NULL</td>
  <td>NULL</td>
  <td>ELectricity</td>
  </tr>
  <tr>
  <td>12422</td>
  <td>June</td>
  <td>2022</td>
  <td>NULL</td>
  <td>NULL</td>
  <td>ELectricity</td>
  </tr>
  <tr>
  <td>12422</td>
  <td>January</td>
  <td>2022</td>
  <td>NULL</td>
  <td>NULL</td>
  <td>Natural Gas</td>
  </tr>
  <tr>
  <td>12422</td>
  <td>February</td>
  <td>2022</td>
  <td>NULL</td>
  <td>NULL</td>
  <td>Natural Gas</td>
  </tr>
  <tr>
  <td>12422</td>
  <td>March</td>
  <td>2022</td>
  <td>NULL</td>
  <td>NULL</td>
  <td>Natural Gas</td>
  </tr>
  <tr>
  <td>12422</td>
  <td>April</td>
  <td>2022</td>
  <td>NULL</td>
  <td>NULL</td>
  <td>Natural Gas</td>
  </tr>
  <tr>
  <td>12422</td>
  <td>May</td>
  <td>2022</td>
  <td>NULL</td>
  <td>NULL</td>
  <td>Natural Gas</td>
  </tr>
  <tr>
  <td>12422</td>
  <td>June</td>
  <td>2022</td>
  <td>NULL</td>
  <td>NULL</td>
  <td>Natural Gas</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>I want it to look like:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>ID</th>
  <th>Month</th>
  <th>Year</th>
  <th>Electricity Use</th>
  <th>Electricity Cost</th>
  <th>Natural Gas Use</th>
  <th>Natural Gas Cost</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>12422</td>
  <td>January</td>
  <td>2022</td>
  <td>NULL</td>
  <td>NULL</td>
  <td>NULL</td>
  <td>NULL</td>
  </tr>
  <tr>
  <td>12422</td>
  <td>February</td>
  <td>2022</td>
  <td>NULL</td>
  <td>NULL</td>
  <td>NULL</td>
  <td>NULL</td>
  </tr>
  <tr>
  <td>12422</td>
  <td>March</td>
  <td>2022</td>
  <td>NULL</td>
  <td>NULL</td>
  <td>NULL</td>
  <td>NULL</td>
  </tr>
  <tr>
  <td>12422</td>
  <td>April</td>
  <td>2022</td>
  <td>13</td>
  <td>44</td>
  <td>NULL</td>
  <td>NULL</td>
  </tr>
  <tr>
  <td>12422</td>
  <td>May</td>
  <td>2022</td>
  <td>NULL</td>
  <td>NULL</td>
  <td>NULL</td>
  <td>NULL</td>
  </tr>
  <tr>
  <td>12422</td>
  <td>June</td>
  <td>2022</td>
  <td>NULL</td>
  <td>NULL</td>
  <td>NULL</td>
  <td>NULL</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>I kind of have it going the way I want with this query</p>
  <pre><code>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
  </code></pre>
  <p>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.</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>ID</th>
  <th>Month</th>
  <th>Year</th>
  <th>Electricity Use</th>
  <th>Electricity Cost</th>
  <th>Natural Gas Use</th>
  <th>Natural Gas Cost</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>12422</td>
  <td>January</td>
  <td>2022</td>
  <td>NULL</td>
  <td>NULL</td>
  <td>NULL</td>
  <td>NULL</td>
  </tr>
  <tr>
  <td>12422</td>
  <td>February</td>
  <td>2022</td>
  <td>NULL</td>
  <td>NULL</td>
  <td>NULL</td>
  <td>NULL</td>
  </tr>
  <tr>
  <td>12422</td>
  <td>March</td>
  <td>2022</td>
  <td>NULL</td>
  <td>NULL</td>
  <td>NULL</td>
  <td>NULL</td>
  </tr>
  <tr>
  <td>12422</td>
  <td>April</td>
  <td>2022</td>
  <td>NULL</td>
  <td>NULL</td>
  <td>NULL</td>
  <td>NULL</td>
  </tr>
  <tr>
  <td>12422</td>
  <td>April</td>
  <td>2022</td>
  <td>13</td>
  <td>44</td>
  <td>NULL</td>
  <td>NULL</td>
  </tr>
  <tr>
  <td>12422</td>
  <td>May</td>
  <td>2022</td>
  <td>NULL</td>
  <td>NULL</td>
  <td>NULL</td>
  <td>NULL</td>
  </tr>
  <tr>
  <td>12422</td>
  <td>June</td>
  <td>2022</td>
  <td>NULL</td>
  <td>NULL</td>
  <td>NULL</td>
  <td>NULL</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>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.</p>