CODEWITHSUNDEEP
sqloracle11g
v8-E
v8-E

Reputation: 1105

How to select same column with multiple alias name from same table?

I need to select values from table with following combinations 

SELECT Details FROM  Avenger   WHERE Id = '1' AND KEY LIKE '11AX';
SELECT Details FROM  Avenger   WHERE Id = '1' AND KEY LIKE '34XA';
SELECT Details FROM  Avenger   WHERE Id = '1' and KEY = '11AZ';
SELECT Details FROM  Avenger   WHERE Id = '1' AND KEY LIKE '11ZA';

In single query I know that I can use 

SELECT * FROM Avenger WHERE ID='1';

But I need to use all selected values and assign it to Four Variable in single query.

any idea or solution I can use ?

Id  Key Details
1   11AX    Mark 
1   34XA    Bruce 
1   11AZ    Mr. Green
1   11ZA    Hulk
2   11AX    Robert jr.
2   34XA    Toney 
2   11AZ    Iron Man
2   11ZA    Arrogant
3   11AX    Thor
3   34XA    Asgard
3   11AZ    God
3   11ZA    Hamer

Upvotes: 0

Views: 2246

Answers (3)

LoztInSpace
LoztInSpace

Reputation: 5697

I think I understand you want this: 

select 
    (SELECT Details FROM  Avenger   WHERE Id = '1' AND KEY LIKE '11AX') as var1,
    (SELECT Details FROM  Avenger   WHERE Id = '1' AND KEY LIKE '34XA') as var2,
    (SELECT Details FROM  Avenger   WHERE Id = '1' and KEY = '11AZ') as var3,
    (SELECT Details FROM  Avenger   WHERE Id = '1' AND KEY LIKE '11ZA') as var4
    from dual

There is also a pivot option

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269503

I think you want a case expression:

select details,
       (case when key like '11AX' then 1
             when key like '34XA' then 2
             when key like '11AZ' then 3
             when key like '11ZA' then 4
        end) as flag
from avenger a
where id = 1;

If you just want to select the value using a single expression, start with in:

where id = 1 and key in ('11AX', '34XA', '11AZ', '11ZA')

If you want to pass in the values as a single string, then regexp_like() works:

where id = 1 and regexp_like(key, '^11AX|34XA|11AZ|11ZA$')

Two notes:

  • The pattern is anchored so it only matches complete key.
  • regexp_like() generally precludes the use of an index, so for performance, = and in are better choices.

Perhaps you intend:

select max(case when key like '11AX' then details end) as deails_11ax,
       max(case when key like '34XA' then details end) as details_34XA,
       max(case when key like '11AZ' then details end) as details_11AZ,
       max(case when key like '11ZA' then details end) as details_11AZ
from avenger a
where id = 1;

This returns one row with four columns.

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520908

We can try using REGEXP_LIKE here with an alternation:

SELECT *
FROM yourTable
WHERE Id = '1' AND REGEXP_LIKE("KEY", '11AX|34XA|11AZ|11ZA')

Actually, I'm not sure you even intended to use LIKE in your original queries. You might really just want to do this:

SELECT *
FROM yourTable
WHERE Id = '1' "KEY" IN ('11AX', '34XA', '11AZ', '11ZA')

Upvotes: 1

Related Questions