Create view from multiple tables, combine values from multiple rows into one row

Question

I have 3 tables as below:

Area table:

UserID | Area
---------------
  1    | 10001
  2    | 10002
  3    | 10003

Info table:

UserID | Info
-----------------
  1    |  U1_Info1
  1    |  U1_Info2
  1    |  U1_Info3
  2    |  U2_Info1
  3    |  U3_Info1

Company table:

UserID | Company
-----------------
  1    |  ComA
  2    |  ComB
  3    |  ComC

After that, I want group by UserID. My expected result as below:

UserID | Area  | Info1    | Info2    | Info3    | Company
----------------------------------------------------------
  1    | 10001 | U1_Info1 | U1_Info2 | U1_Info3 | ComA
  2    | 10002 | U2_Info1 |          |          | ComB
  3    | 10003 | U3_Info1 |          |          | ComC

User 3 doesn't have Info2 and Info3 so I set them = ' '. Can I make a View like that?

Answer 1

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE Area ( UserID, Area ) AS
          SELECT 1, 10001 FROM DUAL
UNION ALL SELECT 2, 10002 FROM DUAL
UNION ALL SELECT 3, 10003 FROM DUAL;

CREATE TABLE Info ( UserID, Info ) AS
          SELECT 1, 'U1_Info1' FROM DUAL
UNION ALL SELECT 1, 'U1_Info2' FROM DUAL
UNION ALL SELECT 1, 'U1_Info3' FROM DUAL
UNION ALL SELECT 2, 'U2_Info1' FROM DUAL
UNION ALL SELECT 3, 'U3_Info1' FROM DUAL;

CREATE TABLE Company (UserID, Company ) AS
          SELECT 1, 'ComA' FROM DUAL
UNION ALL SELECT 2, 'ComB' FROM DUAL
UNION ALL SELECT 3, 'ComC' FROM DUAL;

CREATE VIEW TEST AS
SELECT A.UserID,
       MAX( A.Area    ) AS Area,
       MAX( CASE WHEN I.Info LIKE '%_Info1' THEN I.Info END ) AS Info1,
       MAX( CASE WHEN I.Info LIKE '%_Info2' THEN I.Info END ) AS Info2,
       MAX( CASE WHEN I.Info LIKE '%_Info3' THEN I.Info END ) AS Info3,
       MAX( C.Company ) AS Company
FROM   Area A
       INNER JOIN
       Company C
       ON ( A.UserID = C.UserID )
       LEFT OUTER JOIN
       Info I
       ON ( A.UserID = I.UserID )
GROUP BY
       A.UserID

Query 1:

SELECT * FROM test

Results:

| USERID |  AREA |    INFO1 |    INFO2 |    INFO3 | COMPANY |
|--------|-------|----------|----------|----------|---------|
|      1 | 10001 | U1_Info1 | U1_Info2 | U1_Info3 |    ComA |
|      2 | 10002 | U2_Info1 |   (null) |   (null) |    ComB |
|      3 | 10003 | U3_Info1 |   (null) |   (null) |    ComC |