Maria.Sama Maria.Sama - 1 month ago 8
SQL Question

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

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?

MT0 MT0
Answer

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 |
Comments