Cystack Cystack - 3 months ago 6
SQL Question

How to add a column to a row in a select

Say I have this table

| Col |
-------
| ABC |
| DEF |


What query should I write to obtain this result (not literally this result, but a general way to do that)?

| Col | Col2 |
--------------
| ABC | 0 |
| ABC | 1 |
| DEF | 0 |
| DEF | 1 |

Answer

I would guess that you want to pair two columns, for each combination. Your question is vague and not specific to a problem. That's my assumption.

I guess this query could do:

Select Table1.Col1, Table2.Col2 from Table1 LEFT JOIN Table2 on 1=1

This way, you pair up every row from table1 with every row from table2.


Edit, without table2:

Select Table1.Col1, Constructed.Col1 from Table1 LEFT JOIN 
(Select 1 as Col1 UNION Select 2 as Col1 UNION 
Select 7 as Col1 UNION Select 14 as Col1) Constructed on 1=1
Comments