Using Oracle Combine Three Tables to One with Pivot

Using Oracle combine three tables to one with PIVOT

This should get you going -

CREATE TABLE users
    (user_id varchar2(9), user_name varchar2(11))
;

INSERT ALL 
    INTO users (user_id, user_name)
         VALUES ('1', 'user_1')
    INTO users (user_id, user_name)
         VALUES ('2', 'user_2')
    INTO users (user_id, user_name)
         VALUES ('3', 'user_3')
    INTO users (user_id, user_name)
         VALUES ('4', 'user_4')
SELECT * FROM dual
;

CREATE TABLE exam
    (exam_id varchar2(9), exam_name varchar2(11))
;

INSERT ALL 
    INTO exam (exam_id, exam_name)
         VALUES ('1', 'exam_1')
    INTO exam (exam_id, exam_name)
         VALUES ('2', 'exam_2')
    INTO exam (exam_id, exam_name)
         VALUES ('3', 'exam_3')
SELECT * FROM dual
;

CREATE TABLE exam_user
    (exam_id varchar2(9), user_id varchar2(9), exam_date varchar2(11))
;

INSERT ALL 
    INTO exam_user (exam_id, user_id, exam_date)
         VALUES ('1', '1', '2017')
    INTO exam_user (exam_id, user_id, exam_date)
         VALUES ('1', '2', '2018')
    INTO exam_user (exam_id, user_id, exam_date)
         VALUES ('1', '3', '2017')
    INTO exam_user (exam_id, user_id, exam_date)
         VALUES ('2', '3', '2018')
SELECT * FROM dual
;

Query -

SELECT * FROM (
SELECT U.USER_ID, U.USER_NAME, E.EXAM_NAME,EU.EXAM_DATE
FROM USERS U, EXAM E, EXAM_USER EU
WHERE U.USER_ID = EU.USER_ID(+)
AND E.EXAM_ID(+) = EU.EXAM_ID
ORDER BY U.USER_ID
  )
  PIVOT (MAX(EXAM_DATE) FOR EXAM_NAME IN ('exam_1' as exam_1, 'exam_2' as exam_2,'exam_3' as exam_3))
  order by 1
;

Output -

USER_ID USER_NAME   EXAM_1  EXAM_2  EXAM_3
1   user_1  2017    (null)  (null)
2   user_2  2018    (null)  (null)
3   user_3  2017    2018    (null)
4   user_4  (null)  (null)  (null)

PIVOT with 3 tables and JOIN without number values

I suspect that you want conditional aggregation to pivot your EAV model. The idea is:

select u.id, 
    max(case when dt.name = 'name'      then ud.data end) as name,
    max(case when dt.name = 'last name' then ud.data end) as name,
    max(case when dt.name = 'address'   then ud.data end) as address
from users u 
left join usersdata ud on u.id = ud.idusers
left join datatypes dt on ud.iddatatypes = dt.id
group by u.id

Joining three tables which include where clauses each

Your have three options to join tables with WHERE clauses:

1 Join the tables and put all the where clauses at the end (as @Amir Pashazadeh has answered.

SELECT *
 FROM table1 JOIN table2 
WHERE conditions_on_table1
  AND conditions_on_table2 ...

2 Join subqueries including where conditions

SELECT *
 FROM table1
 JOIN (SELECT columns_I_need
        FROM table2
       WHERE conditions_on_table2)
   ON table1.col = table2.col

3 Use the WITH clause to chop the query into bits that are easier to understand (and test).

I would normally use option 3 for such a query. You seem to use the tables ti_kayttajantyoyksikko and ti_esimiehentyoyksikko twice, so I'd stick them in a subquery:

WITH 
  my_unit AS (
    SELECT t1.ti_kt_kayttajaid AS USER_ID
      FROM ti_kayttajantyoyksikko t1
      JOIN ti_esimiehentyoyksikko t2
        ON t1.ti_kt_tyoyksikko = t2.ti_et_tyoyksikkoid
      WHERE t2.ti_et_kayttajaid = 14794)
SELECT * FROM my_unit;

Once that does what I expect, I'd add the next part:

WITH 
  my_unit AS (
    SELECT t1.ti_kt_kayttajaid AS USER_ID
      FROM ti_kayttajantyoyksikko t1
      JOIN ti_esimiehentyoyksikko t2
        ON t1.ti_kt_tyoyksikko = t2.ti_et_tyoyksikkoid
      WHERE t2.ti_et_kayttajaid = 14794),
  my_users AS (
    SELECT k.ti_ka_kayttajaid as USER_ID
           k.ti_ka_etunimi    as USER_NAME
      FROM ti_kayttaja k
      JOIN my_unit u ON k.ti_ka_kayttajaid = u.user_id)
SELECT * FROM my_users;

... then ...

WITH 
  my_unit AS (
    SELECT t1.ti_kt_kayttajaid AS USER_ID
      FROM ti_kayttajantyoyksikko t1
      JOIN ti_esimiehentyoyksikko t2
        ON t1.ti_kt_tyoyksikko = t2.ti_et_tyoyksikkoid
      WHERE t2.ti_et_kayttajaid = 14794),
  my_users AS (
    SELECT k.ti_ka_kayttajaid as USER_ID
           k.ti_ka_etunimi    as USER_NAME
      FROM ti_kayttaja k
      JOIN my_unit u ON k.ti_ka_kayttajaid = u.user_id)
  my_pvm AS (
    SELECT PVM.PVM.ti_sk_kayttajaid AS USER_ID
           PVM.ti_sk_paivamaara     AS SITOUMUS_DATE
      FROM ti_sitoumus_kayttaja PVM
      JOIN my_unit u ON k.ti_kt_tyoyksikko = u.user_id)
SELECT * 
  FROM      my_users 
  LEFT JOIN my_pvm USING (user_id);

... and finally ...

WITH 
  my_unit AS (
    SELECT t1.ti_kt_kayttajaid AS USER_ID
      FROM ti_kayttajantyoyksikko t1
      JOIN ti_esimiehentyoyksikko t2
        ON t1.ti_kt_tyoyksikko = t2.ti_et_tyoyksikkoid
      WHERE t2.ti_et_kayttajaid = 14794),
  my_users AS (
    SELECT k.ti_ka_kayttajaid as USER_ID
           k.ti_ka_etunimi    as USER_NAME
      FROM ti_kayttaja k
      JOIN my_unit u ON k.ti_ka_kayttajaid = u.user_id)
  my_pvm AS (
    SELECT PVM.PVM.ti_sk_kayttajaid AS USER_ID
           PVM.ti_sk_paivamaara     AS SITOUMUS_DATE,
           PVM.ti_sk_sitoumusid     AS SITOUMUS_ID
      FROM ti_sitoumus_kayttaja PVM
      JOIN my_unit u ON k.ti_kt_tyoyksikko = u.user_id),
  my_exam AS (
    SELECT EXAM.ti_su_sitoumusid as SITOUMUS_ID, 
           EXAM.ti_su_nimitys    as SITOUMUS_NAME
      FROM ti_sitoumus EXAM
     WHERE EXISTS (
             SELECT *
               FROM ti_kayttooikeus_ryhma t3
               JOIN ti_esimiehentyoyksikko t4
                 ON t3.ti_kr_kohdeid = t4.ti_et_tyoyksikkoid
              WHERE t3.ti_kr_kayttokohde = 6
                AND t4.ti_et_kayttajaid = 14784
                AND EXAM.ti_su_sitoumusid = t3.ti_kr_kayttokohdeid))
SELECT user_id, user_name, sitoumus_id, sitoumus_name, sitoumus_date
  FROM my_users 
  LEFT JOIN my_pvm  USING (user_id)
  LEFT JOIN my_exam USING (sitoumus_id)
 ORDER BY user_id;

sql select pivot multiple tables

Pivot example.

SELECT 
    t.ProductName,
    [Live] AS LiveCapacity,
    [Pending] AS PendingCapacity
FROM 
(
    SELECT  product.ProductName,
          performance.Capacity,
          plan.Status 
    FROM    product 
          JOIN performance ON performance.ProductID = product.ProductID
          JOIN plan ON performance.PlanID = plan.PlanID
) t
PIVOT (
    SUM(Capacity)
    FOR Status IN ([Live],[Pending])
) p

Non-Pivot example

SELECT  product.ProductName,
        SUM(CASE WHEN plan.Status = 'Live' THEN performance.Capacity END) as LiveCapacity,
        SUM(CASE WHEN plan.Status = 'Pending' THEN performance.Capacity END) as PendingCapacity
FROM    product 
        JOIN performance ON performance.ProductID = product.ProductID
        JOIN plan ON performance.PlanID = plan.PlanID
GROUP BY product.ProductName

Oracle create a Pivot table from 3 columns

You tagged Oracle18c so this should work for your version. I tested this on 11g.

SELECT *
FROM (
  SELECT product_code, order_type, income
  FROM Logistics
)
PIVOT (
  sum(income)  
  for order_type
  IN ('EB' AS EB, 'ER' AS ER, 'ES' AS ES, 'EK' AS EK)
);

This does require the set for the IN list to be filled in prior to execution. There is another syntax that allows a sub select but it returns XML. If you try to replace PIVOT XML with PIVOT it gives an error.

WITH orderTypes AS
(
    select 'EB' as order_type from dual union all
    select 'ER' as order_type from dual union all
    select 'ES' as order_type from dual union all
    select 'EK' as order_type from dual union all 
    select 'AA' as order_type from dual union all
    select 'AB' as order_type from dual union all
    select 'AC' as order_type from dual union all
    select 'AD' as order_type from dual union all
    select 'AE' as order_type from dual        

)
SELECT *
FROM (
  SELECT l.product_code, l.order_type, l.income
  FROM Logistics l
)
PIVOT XML  (
  sum(income) AS orderSum
  for order_type 
  IN ( select order_type from orderTypes)
);

Pivot in Oracle based on multiple columns

You can definitely use multiple columns within a PIVOT clause, here is an example using your table setup:

SELECT *
FROM demo
PIVOT (MAX(VALUE) FOR (identifier_1, identifier_2) IN ((3000, 23) AS A3000_23, (3000, 24) AS A3000_24,
                                                        (3001, 25) AS A3001_25, (3001, 26) AS A3001_26));

N.B.: Excuse the "A" in the column names, you need to start the identifier with a character, not a number.

Here is a DBFiddle showing the results (LINK)

Obviously you can see how this would quickly grow out-of-hand if you need to list large amounts of PIVOT columns.

SQL PIvot for a complex join

You can do it with a CROSS join of the tables and conditional aggregation:

SELECT e.EMPLOYEEID
FROM managers m CROSS JOIN employees e
WHERE m.MANAGERID = 1
GROUP BY e.EMPLOYEEID
HAVING COUNT(DISTINCT m."GROUP") = 
       COUNT(CASE WHEN e."GROUP" = m."GROUP" AND  e."VALUE" = m."VALUE" THEN 1 END)

See the demo.

Results:

---