You cannot output multiple independent results from a single SELECT statement. But sometimes I wish that could be done. An extremely simple case is when you need to build the initial dimensions from a very large table.
Typically, these dimension columns have very low selectivity and are often not indexed. When you want to get all the distinct values from each of these columns, you may want to do the following:
SELECT DISTINCT c1 FROM VeryLargeTable;
SELECT DISTINCT c2 FROM VeryLargeTable;
…
SELECT DISTINCT c30 FROM VeryLargeTable;
The problem is that this would require scanning the table 30 times (assuming you have 30 such columns to query). And if each scan takes 30 minutes, these queries would 30*30 = 900 minutes or 15 hours to finish.
Wouldn’t it be nice to be able to say something like (I’m making up the syntax) the following?
(SELECT DISTINCT c1) ||
(SELECT DISTINCT c2) ||
…
(SELECT DISTINCT c30)
FROM VeryLargeTable;
And have it produce 30 independent results, one for each column, with a single scan through the large table. This could be done in 30 minutes instead of 15 hours.
Yes, there are tricks you can play with to reduce the amount of time in getting these 30 resultsets, but none of which is as elegant as producing the results with a single scan.
By the way, I didn't just make up the above example. Scanning the very large table multiple times, each for a separate column, is how SQL Server Analysis Server does the initial dimension processing (although there are options to tweak it).