How to find DISTINCT values from columns with similar names

Question

I've read many posts with similar questions, but haven't found an answer yet. I'm quite new to SQL. Using SQL Server Express 2008.

My goal is to get a single-column result of all values that are distinct among several columns with similar names. Related columns have the same base name (suffix), followed by an integer. I have many groups of columns, so I don't want to hard-code the query.

Fruit1 Coating1 Temperature1   Fruit2 Coating2  Temperature2 Fruit3 Coating3  Temperature3
-----------------------------------------------------------------------------------
apple  caramel  72.5           pear   chocolate  74.1        apple  chocolate  98.6
pear   caramel  73.3           peach  chocolate  42.7        apple  chocolate  33.0

So I want the server to return, for example, all the fruits used in any/all fruit columns apple peach pear

I already know how to get a list of the column names using a wildcard:

SELECT Column_name AS columnNames
FROM INFORMATION_SCHEMA.COLUMNS
WHERE Column_name LIKE 'Fruit%' 

I also know how to find distinct values from columns if I already know the column names:

SELECT DISTINCT Fruit FROM(
SELECT Fruit1 as Fruit from FruitBasket
UNION
SELECT Fruit2 as Fruit from FruitBasket
UNION
SELECT Fruit3 as Fruit from FruitBasket)
AS finalOutput

What I need to know is how to use the column-name results from the first query in the UNION parameters of the second. Should I use a FOR loop or something?

Thanks!

Answer 1

Edit:

The user mentioned in a comment that he'd be open to a new design.

Click here for a working example of the below code

I agree it's a bad design. I don't know how to make it better.

Here's how you could improve your design:

    CREATE TABLE fruit
(
  fruit_id INT CONSTRAINT pk_fruit_pid PRIMARY KEY,
  fruit_name VARCHAR(25)
);

CREATE TABLE temperature
(
  temperature_id INT CONSTRAINT pk_sprinkle_pid PRIMARY KEY,
  temperature_value DECIMAL(18,1)
);

CREATE TABLE coating
(
  coating_id INT CONSTRAINT pk_coating_pid PRIMARY KEY,
  coating_name VARCHAR(25)
);

CREATE TABLE fruit_temperature_coating
(
  fruit_id INT CONSTRAINT fk_fruit_fid FOREIGN KEY REFERENCES fruit(fruit_id),
  temperature_id INT CONSTRAINT fk_temp_fid FOREIGN KEY REFERENCES temperature(temperature_id),
  coating_id INT CONSTRAINT fk_coating_fid FOREIGN KEY REFERENCES coating(coating_id)
);

Run the Query:

     SELECT f.fruit_name, 
       t.temperature_value, 
       c.coating_name 
FROM   fruit_temperature_coating AS fpc
       INNER JOIN Fruit AS f 
               ON fpc.fruit_id = f.fruit_id 
       INNER JOIN temperature AS t
               ON fpc.temperature_id = t.temperature_id 
       INNER JOIN coating AS c 
               ON fpc.coating_id = c.coating_id 

Answer 2

I agree that the database design should be improved, but if you were handed a giant project and need the data now, you can do some crazy stuff with cursors/dynamic sql/temp tables:

GO

DECLARE @sql varchar(500) = '',
        @currentColumn varchar(50)


CREATE TABLE #fruit (Fruit varchar(20))

DECLARE fruit_cursor CURSOR FOR
    SELECT c.name
    FROM sys.tables t
        INNER JOIN sys.columns c
            on t.object_id = c.object_id
    WHERE t.name = 'TABLE NAME HERE'
    AND c.name like 'Fruit%'

OPEN fruit_cursor

FETCH NEXT FROM fruit_cursor INTO @currentColumn

WHILE @@FETCH_STATUS = 0 BEGIN

    SET @sql = 
        'INSERT INTO #fruit ' +
        'SELECT @columnName ' +
        'FROM TABLE NAME HERE'

    SET @sql = REPLACE(@sql, '@columnName', @currentColumn)

    EXEC(@sql)

    FETCH NEXT FROM fruit_cursor INTO @currentColumn
END

CLOSE fruit_cursor

DEALLOCATE fruit_cursor

SELECT DISTINCT Fruit  
FROM #fruit
ORDER BY Fruit

DROP TABLE #fruit 

For the future, though, I'd highly recommend looking into many-to-many relationships.

Answer 3

Thanks to everyone for your advice.

First of all, I have created an ugly, but working, solution to my original question, based on replies I received. It basically executes a query string assembled from another query of similarly-named columns. The output is, indeed, a list of all distinct values from multiple columns with similar names. This code is pasted below.

However, as y'all said, the whole schema is bad. If I try to do anything useful with those results, it becomes very complicated because I need to track which column number a distinct value belonged to. Trying to sort or compare data in correspondingly-numbered columns is even more crazy because I can't be sure if Apples were always put in "Fruit1" and Peaches in "Fruit2", or the other way around, etc.

After sleeping on it, I came up with a better solution, which is to create another, single, table of all "fruit basket" data that (while perhaps not "many-to-many") is doubly-linked from the "event history" row to a row in the "fruit basket" table, so I can easily search through all fruit baskets for particular info and then follow the link back to the event history. This way, if I find out that apples are toxic above 398.2 degrees, I can easily locate all events where apples so hot were sent, or I can sort all events with apples, by temperature.

This will take some rewriting in my application code, but it's better to fix it now. :-)

Here is my code for finding distinct values in similarly-named columns, but I won't be using this in my project after-all, as stated above.

DECLARE @sql varchar(MAX) = 'SELECT DISTINCT FriutBasketInfo FROM(',
    @currentColumn varchar(50)

DECLARE fruit_cursor CURSOR FOR    
    SELECT Column_name AS columnNames
    FROM INFORMATION_SCHEMA.COLUMNS
    --WHERE Column_name LIKE 'Fruit%' 
    --WHERE Column_name LIKE 'Coating%' 
    WHERE Column_name LIKE 'Temperature%'    

OPEN fruit_cursor

FETCH NEXT FROM fruit_cursor INTO @currentColumn
SET @sql = @sql + 'SELECT ' + @currentColumn + ' as FriutBasketInfo from FruitBasketHistoryTable '
FETCH NEXT FROM fruit_cursor INTO @currentColumn

WHILE @@FETCH_STATUS = 0 BEGIN    
    SET @sql = @sql + ' UNION SELECT ' + @currentColumn + ' as FriutBasketInfo from FruitBasketHistoryTable '
    FETCH NEXT FROM fruit_cursor INTO @currentColumn    
END

SET @sql = @sql + ') AS finalOutput'

EXEC(@sql)

CLOSE fruit_cursor
DEALLOCATE fruit_cursor