How to select next column when previous column meets certain condition using SQL

I'm using SQL on Impala

The table I'm querying on looks like

customer name shop1 shop1number shop2 shop2number shop3 shop3number

TOM AB 111 AA 231 AC 321

AMY AC 121 AB 213 AD 231

Franck AD 123 AE 233 AB 234

enter image description here here, the numbers are customer loyalty number and the challenge is if I'm looking for the loyalty number of shop 1 (AB), I have no idea which column it falls in as when customer fills in their loyalty number, it's their choice to put in the number at whatever order they profile

Solution

If i understand you correctly, you are looking for all the loyalty numbers associated with a shop, so one way could be to bring rows data to columns first using union all and then search for a shop; lets say AB.

select * from
(
select customername, shop1 as shop, shop1number as shopnumber
from table1
union all
select customername, shop2 as shop, shop2number as shopnumber
from table1
union all
select customername, shop3 as shop, shop3number as shopnumber
from table1
    ) t
where t.shop = 'AB';

Result:

+--------------+------+------------+
| customername | shop | shopnumber |
+--------------+------+------------+
| AMY          | AB   |        213 |
| TOM          | AB   |        111 |
| Franck       | AB   |        234 |
+--------------+------+------------+

DEMO