basic sql : selecting the same column multiple times in one query, when each occurrence is dependent on different where clause

Question

What is the best way to to perform this query. I have the following table

mytable with columns

x y 
1 a
2 b
3 c

and I would like to (in pseudo sql)

select x as x1 ,x as x2, x as x3 from mytable where ????

when

x1 is x where y=a

x2 is x where y=b

x3 is x where y=c

so I would like as a result

1, 2, 3

I am currently using cte's and and a very large dataset, I am trying to reduce the query time, is it always necessary to have 3 table scans ?

Answer 1

You should use 3 queries. It will be a lot faster with proper indexing when self joins. Additionally it will be more readable.

If you would like one query call, it might be this :)

SELECT
(SELECT x FROM table WHERE y=1) AS x1,
(SELECT x FROM table WHERE y=2) AS x2,
(SELECT x FROM table WHERE y=3) AS x3

Answer 2

I would of done this :

SELECT
    tableRowA.x as x1
    tableRowB.x as x2
    tableRowC.x as x3
FROM
    table as tableRowA,
    table as tableRowB,
    table as tableRowC
WHERE
    tableRowA.y = 1
    tableRowB.y = 2
    tableRowC.y = 3

Bit easier to understand, and pull out more info if you need multiple columns from each row