I want to count how much common projects companies have with each other, like:

Is there a easy to go method in SQL to do so? With the output like that?

CodePudding user response：

There's a simple way, but - output isn't exactly as you'd want it to. It shows pairs and number of common projects (the cnt column):

SQL> select a.company, b.company, count(distinct b.participation) cnt
  2  from project a join project b on a.company < b.company
  3  group by a.company, b.company;

COMPANY   COMPANY          CNT
--------- --------- ----------
Apple     Tesla              1
Apple     Microsoft          2
Microsoft Tesla              1

SQL>

CodePudding user response：

It's possible to do a self-join in the source query of a pivot.

SELECT *
FROM (
  SELECT t1.Company
  , t2.Company AS Company2
  , COUNT(t1.Participation) AS Participations
  FROM CompanyProjectParticipations t1
  JOIN CompanyProjectParticipations t2
    ON t2.Participation = t1.Participation
   AND t2.Company != t1.Company
  GROUP BY t1.Company, t2.Company
) Src
PIVOT (
  SUM(Participations)
  FOR Company2 IN ([Apple], [Microsoft], [Tesla])
) Pvt
ORDER BY Company;

Company	Apple	Microsoft	Tesla
Apple	null	2	1
Microsoft	2	null	1
Tesla	1	1	null

Test on db<>fiddle here