How to select multiple columns based on a condition in SQL?-CodePudding

I have a table, the columns are like this:

| id | position | A | B | C | D | E |

I'm selecting by id. If the position is '1', it should return column A, B, C. If not, it should return column D, E

For example, if the table is:

| id | position | A | B | C | D | E |
|  0 |     1    | a | b | c | d | e |
|  1 |     2    | a | b | c | d | e |

When the query selects id=0, the result should be:

|  0 |    1    | a | b | c |

When the query selects id=2, the result should be:

|  1 |    2    | d | e |

How should I write the SQL query?

CodePudding user response：

you can't write a query to select columns according to selected rows. but if you want specific columns you can try this:

SELECT id, position, A, B, C
FROM tablename
WHERE id = 1

CodePudding user response：

Try this...

select id, position,
if (id=0, A, D),
if (id=0, B, E),
if (id=0, C, "")
from Table1

It's a little inflexible but it's a start. You can play with it here: http://sqlfiddle.com/#!9/d47c33/10/0

CodePudding user response：

You cannot have a result set with a different number of columns per row, so it will have to be | 1 | 2 | d | e | NULL |.

To get what you want you can make two queries and UNION them together. Otherwise you could make a CASE statement for each column, but don’t do this if you don’t have to.

If you have any choice in the matter, I would recommend just making two queries and process the results separately.

But the UNION:

select id, position, a, b, c
from cool_table
where id in (1, 2, 3, 4, 5)
   and position = 1

UNION

select id, position, d, e, null
from cool_table
where id in (1, 2, 3, 4, 5)
   and position <> 1

Really though, this seems like you’re going “against the grain”. Perhaps some improvement can be made elsewhere that will make this unnecessary.