SQL using where and as multiple times in one column

I am trying to create multiple columns from one column creating different column names. I'll post something I tried but didn't work -

SELECT sample, specimen_id
     , (amp as amp1 from schema.table where amp1 ilike 'amp1%' and read >= 100), 
    (amp as amp2 from schema.table where amp ilike 'amp2%' and read >= 100)
    from schema.table

I also tried:

SELECT sample, specimen_id
    , amp as amp1 from schema.table where amp1 ilike 'amp1%' and read >= 100, 
    AND amp as amp2 from schema.table where amp ilike 'amp2%' and read >= 100
    from schema.table

I have to do this for 5 different genes. But basically I just need to chop up the "amp" column based on it's first few characters.

Thanks in advance for your help!

Solution

Your code is syntactically incorrect, but you also have a logical error. First, let's delve into the logical error.

When you consider a record in a table and load its fields, such as sample and specimen_id, you cannot link these values to your amp value, unless there is a single such value. Since you attempted to write a subselect, there is no guarantee that amp will be unique, so, you attempt to link a record to possibly multiple records.

You will need to perform some join, like

SELECT t.sample, t.specimen_id, t1.amp as amp1, t2.amp as amp2
from schema.table t
join schema.table t1
on t.sample = t1.sample and t.specimen = t.specimen,
join schema.table t2
on t.sample = t2.sample and t.specimen = t2.specimen,
where (t1.amp ilike 'amp1%' and t1.read >= 100) and
      (t2.amp ilike 'amp2%' and t2.read >= 100)

But it's possible that you want to group by them in some manner, like:

SELECT t.sample, t.specimen_id, group_concat(t1.amp) as amp1, group_concat(t2.amp) as amp2
from schema.table t
join schema.table t1
on t.sample = t1.sample and t.specimen = t1.specimen,
join schema.table t2
on t.sample = t2.sample and t.specimen = t2.specimen,
where (t1.amp ilike 'amp1%' and t1.read >= 100) and
      (t2.amp ilike 'amp2%' and t2.read >= 100)
group by t.sample, t.specimen_id

The functions above are MySQL functions, if you are using a different RDBMS, then you can change the call to these functions to the ones that actually exist in your RDBMS.