Is there a UNION ALL equivalent for columns?
Is there a UNION ALL equivalent for columns?


You are talking about the usage of a UNION in a CTE to generate dates. Here I show how you can generate dates in columns.

You can use a PIVOT Clause. However, there is a limit: The column names must be known in advance. If, for instance, you want to display the data per month, you can use the day numbers as column names:

SELECT *
FROM
    (SELECT DAY([Date]) AS d,
            MONTH([Date]) AS m,
            YEAR([Date]) AS y,
            Amount
     FROM MyTable) src
PIVOT (
    SUM(Amount)
    FOR d IN ( [1],  [2],  [3],  [4],  [5],  [6],  [7],  [8],  [9], [10],
              [11], [12], [13], [14], [15], [16], [17], [18], [19], [20],
              [21], [22], [23], [24], [25], [26], [27], [28], [29], [30], [31])
) piv;


The result looks like this (I clipped the image at 11 days but in reality, there are columns up to 31):



If you must display more dates, you can use the number of days since the start date as column names instead. If you create a report (using some reporting tool) based on this query, you can add this number to the start date to get real dates again for the columns headers.

Since you must specify the column names explicitly, no CTE is necessary for this part, but you can combine the pivot query with a CTE to generate the rows.