Bring repeat records from 3 columns in the same table

Question

I have a table with transport voucher data of my company employees:

nome ! valor1 ! valor2 ! valor3

teste1 ! 6.50 ! 0.00 ! 0.00
teste2 ! 4.30 ! 2.80 ! 1.10
teste3 ! 8.40 ! 1.10 ! 0.00
teste4 ! 2.85 ! 1.10 ! 0.00

where it is possible for the employee to take up to 3 bus lines to come to work, so I have these three columns.

The problem I’m going through is this, I want to return the data of the three columns without repetition, where I have all the values of the employees' bus lines.

I need to return if possible in a single column the data without repeating, no values '0.00' and ascending order :

outworking

1.10
2.80
2.85
4.30 
6.50 
8.40

Answer 1

UNION: to bring the three together.

GROUP BY: to unite the equal results.

SELECT * FROM (
SELECT valor1 valor FROM TABELA
UNION
SELECT valor2 valor FROM TABELA
UNION
SELECT valor3 valor FROM TABELA) TABELAX
WHERE valor > 0
GROUP BY valor
ORDER BY valor

TABELAX: alias.

If the columns valor1, valor2 and Valor3 had only one value and the other 0.00, you could use CASE, doing everything in one query.

Also create a VIEW select if always used.

Answer 2

You can make a query per column, join all in one query UNION, sorting and grouping the result:

SELECT * FROM (
    SELECT valor1 AS valor FROM tabela
    UNION ALL
    SELECT valor2 AS valor FROM tabela
    UNION ALL
    SELECT valor3 AS valor FROM tabela        
) AS resultado
WHERE valor > 0
GROUP BY valor
ORDER BY valor;

Behold spinning.