0

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)

0
2

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.

3
  • Using a dynamic pivot you could generate columns on the fly. But this means that you couldn't run this query in a function. I take it there is no way to achieve this without knowing the columns in advance?
    – Zach Smith
    Dec 30 '17 at 7:12
  • 1
    I fear that you are reaching the limits of SQL. Often such complex tasks are better done in the font-end. Dec 30 '17 at 14:37
  • That would result in duplicated code on different clients though. For example a web page and excel sheet. I would choose to keep as much logic in the database as possible
    – Zach Smith
    Dec 31 '17 at 6:40
2

Community wiki answer:

It's possible to simulate a loop in SQL Server with a self-referencing CTE and a union all...

This will quickly hit the recursion limit on CTE's, usually 100 by default. Though the limit can be extended using the MAXRECURSION query hint, there will be a performance hit.

MAXRECURSION number
Specifies the maximum number of recursions allowed for this query. number is a nonnegative integer between 0 and 32767. When 0 is specified, no limit is applied. If this option is not specified, the default limit for the server is 100.

Research Tally Table and Numbers Table for better, more efficient, ways to do this. Here's a link about tally/numbers tables, and here's a link about creating a calendar table.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.