collapsing two columns in one in sql?

Question

Hello all!

Say I have a table like that:

|x1|y1|

|0 |1 |

|2 |4 |

|3 |2 |

I know I can do something like that:

SELECT a.x1 AS .., a.y1 AS .., 
       b.x1 AS .., b.y1 AS .., 
       c.x1 AS .., c.y1 AS .., 
       d.x1 AS .., d.y1 AS .. 
FROM xytable a, xytable b, xytable c, xytable d 
WHERE ...

In my result table, however, I would like to output a.x1 and a.y1 together, as a a tuple under one column. In other words, I want to have:

|c1          |c2            |c3             |c4           |

(a.x1, a.y1) | (a.x2, a.y2) |  (a.x3, a.y3) | (a.x4, a.y4)

Is it possible?

Answer 1

As a character string?

You can do something like

SELECT '(' + convert(varchar, x1) + ',' + convert(varchar, y1) + ')' as "col"

Answer 2

If you are using MySQL, you could use the CONCAT function:

SELECT 
     CONCAT('(', a.x1, ', ', a.y1, ')') AS c1
FROM 
    xytable a, xytable b, xytable c, xytable d 
WHERE 
    ...

In SQL Server, you can concatenate with the + operator to concatenate strings:

SELECT 
    '(' + a.x1 + ', ' + a.y1 + ')' AS c1
FROM 
    xytable a, xytable b, xytable c, xytable d 
WHERE 
    ...