Return multiple tables specific to each column

I would like to get a more simple query that runs through all columns of a table and returns the SAME number of tables for EACH column.

For example…

select distinct a from tbl;
select distinct b from tbl;
select distinct c from tbl;
select distinct d from tbl;

would return four tables if ran at once. This gives the results I would like but I would have to type out each column, being especially pesky if I had many many columns. How can I write a query that would go through all the columns and give me the same results without having to do this?

Answer

Based on our conversation in the comments, you are happy to use dynamic TSQL and you have permissions to execute it on your server.

Therefore, one way to achieve this is to enumerate the columns using a cursor and then execute some dynamic TSQL to get the result set for each column.

The following example shows how it can be done. Simply modify @table to be the name of whichever table you want and it will output every column as a single result set:

DECLARE @column VARCHAR(100)
DECLARE @s VARCHAR(200)
DECLARE @table VARCHAR(50)
SET @table = 'tbl'

DECLARE curRead CURSOR FORWARD_ONLY READ_ONLY
  FOR
    SELECT COLUMN_NAME
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = @table

OPEN curRead
FETCH NEXT FROM curRead INTO @column  

WHILE @@FETCH_STATUS = 0  
BEGIN  
    SET @s = 'SELECT ' + @column + ' FROM ' + @table
    EXEC(@s)

    FETCH NEXT FROM curRead INTO @column 
END 
CLOSE curRead  
DEALLOCATE curRead

In this case, the CURSOR enumerates all of the columns that belong to your table.

You then iterate through these, storing the column name (from COLUMN_NAME in a variable @column), build a piece of dynamic SQL (is @s) and then execute it.