CODEWITHSUNDEEP
oracle-databaseviewdynamicquery
Santosh Reddy Kommidi
Santosh Reddy Kommidi

Reputation: 23

Oracle - Create a view with data from different table as column names

Create view with fields from another table as column headers

I've got two tables that I'd like to combine into a view.

The first table contains the structure:

PhoneNumberType PhoneNumberTypeName

101 Mobile

102 Home

103 Work

The second table contains the actual data that will be stored, and the columns reference the ID of the first table:

PhoneNumberType PhoneNumber PersonId

101 90354351 1001

102 98345634 1001

103 92345123 1002

What I'd like to get as a result in a view:

Mobile Home Work PersonId

9035435 98345634 Null 1001

Null Null 92345 1002

Basically I would like to combine 2 tables and create a view.

Upvotes: 0

Views: 391

Answers (1)

Popeye
Popeye

Reputation: 35930

You can use PIVOT in this case as following:

-- CREATING SAMPLE DATA
WITH FIRST_TABLE(PhoneNumberType, PhoneNumberTypeName)
AS
(
SELECT 101, 'Mobile' from dual union all
SELECT 102, 'Home' from dual union all
SELECT 103, 'Work' from dual
),
second_table (PhoneNumberType, PhoneNumber, PersonId)
AS
(
select 101, 90354351, 1001 from dual union all
select 102, 98345634, 1001 from dual union all
select 103, 92345123, 1002 from dual
)
--
-- ACTUAL QUERY STARTS FROM HERE
SELECT
    *
FROM
    (
        SELECT
            S.PERSONID,
            S.PHONENUMBER,
            F.PHONENUMBERTYPENAME
        FROM
            FIRST_TABLE F
            JOIN SECOND_TABLE S ON F.PHONENUMBERTYPE = S.PHONENUMBERTYPE
    ) PIVOT (
        MAX ( PHONENUMBER )
        FOR PHONENUMBERTYPENAME
        IN ( 'Mobile',
        'Home',
        'Work' )
    )
ORDER BY
    1

db<>fiddle demo

Cheers!!

Upvotes: 1

Related Questions