Rename Column Depending on Value-CodePudding

I have a table likeso:

Roll    Name    Type
3       Mark    Ultimate Internet
1       Ana     Chase
4       June    Ultimate Internet
2       Badol   Chase

How can I make a SELECT statement where I rename the "Type" column depending on a value? Something like:

SELECT Roll, Name,
    IF Type = 'Ultimate Internet' AS 'ISP' END IF
    IF Type = 'Chase' AS 'Bank' END IF
FROM MyTable

If Type = 'Ultimate Internet', the result would be:

Roll    Name    ISP
3       Mark    Ultimate Internet
4       June    Ultimate Internet

If Type = 'Chase', the result would be:

Roll    Name    Bank
1       Ana     Chase
2       Badol   Chase

CodePudding user response：

No, because column names have to be known at compile time.

I am having trouble understanding what you're doing. You use Type as both a column name and a variable at the same time? Is this in PL/SQL? If so, you could have two different cursors, but then you're not displaying the output like this. If this is pure SQL, then where is the variable coming from?

CodePudding user response：

The column names need to be static. Instead of trying to dynamically build a single query, you can use two queries:

SELECT Roll, Name, type AS ISP
FROM   MyTable
WHERE  type = 'Ultimate Internet'

and:

SELECT Roll, Name, type AS bank
FROM   MyTable
WHERE  type = 'Chase'

You could even create a lookup table with the type values:

CREATE TABLE types (
  type       VARCHAR2(20) PRIMARY KEY,
  super_type VARCHAR2(20)
);

INSERT INTO types (type, super_type)
SELECT 'Ultimate Internet', 'ISP'  FROM DUAL UNION ALL
SELECT 'Chase',             'bank' FROM DUAL;

and then use:

SELECT m.Roll, m.Name, m.type AS ISP
FROM   MyTable m
       INNER JOIN types t
       ON (m.type = t.type)
WHERE  t.super_type = 'ISP'

SELECT m.Roll, m.Name, m.type AS bank
FROM   MyTable m
       INNER JOIN types t
       ON (m.type = t.type)
WHERE  t.super_type = 'bank'