SQL Simple Pivot On Date And Sums

[Heretic86]

/* Missing Fields? */
SELECT myDate, Hours FROM
(
  SELECT
    CONVERT(NVARCHAR(MAX), myDate) AS myDate,
    [Hours] 
  FROM myTable
) AS PivotSource
PIVOT
(
  SUM([Hours])
  FOR [myDate]
  /* What Goes Here? */
  IN ([1], [2], [3], [4], [5], [6], [7])
) AS PivotTable

Hi all!

Not sure what I am doing wrong, Im trying to Pivot with my Sum Results going into a Date Column for the totals of Hours

What am I doing wrong?

The result that I want should look like this:

2021-09-21  2021-09-22  2021-09-23  (Next Date)  (Next Date 2)
       8.5           0        5.75  (Hours Sum)  (Hours Sum 2)

 

How can I do this?

[Barand]

If all you want is a row of date headings and a single row of totals, using PIVOT seems like overkill.

<?php
    
    $res = $con->query("SELECT pay_date
                             , SUM(amount) as total
                        FROM income
                        GROUP BY pay_date
                        ");
    $data = $res->fetchAll();
    $heads  = "<tr><td>" . join('</td><td>', array_column($data, 'pay_date')) . "</td></tr>\n";
    $totals = "<tr><td>" . join('</td><td>', array_column($data, 'total')) . "</td></tr>\n";

?>

    <table border='1'>
    <?=$heads?>
    <?=$totals?>
    </table>

[Heretic86]

You got the simplified version of what I was trying to do.  I just needed a foundation of getting a Pivot to work.  Add in N number of columns, and make the column names dynamic as well as aggregated sums from stepped groups.