SQL Server – Is There a UNION ALL Equivalent for Columns?

ctesql serversql-server-2016

It's possible to simulate a loop in SQL Server with a self-referencing CTE and a union all, something like the following:

declare @a int = 1;
declare @b int = 5;

;with Tbl as (
    select @a a
    UNION ALL
    select (a + 1) a
    from Tbl
    where (a + 1) < @b
)
select * from Tbl

This is useful when you want to create a list of dates from a start date to an end date and then join on those specific dates.

I could also imagine wanting to do that for columns as well. Is there a means of achieving this in SQL Server without using dynamic SQL? I would probably want to save such a query as a view, and not an SP (though I know that this could be achieved via dynamic SQL)

Best Answer

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):

result set of query

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.

Related Question