JOIN TWO TABLES

Question

I have two tables: 'Project' and 'Project Monthly'.

| PROJECT_ID |  TITLE |    | ID | PROJECT_ID |  MONTH | EXPENSE |
-----------------------    --------------------------------------
|          1 | title1 |    |  1 |          1 |      1 |     100 |
|          2 | title2 |    |  2 |          1 |      2 |    2000 |
|          3 | title3 |    |  3 |          1 |      3 |     900 |
                           |  4 |          1 |      4 |     900 | 
                           |  5 |          2 |      1 |     200 |
                           |  6 |          2 |      2 |     200 |
                           |  7 |          3 |      1 |     500 |

I would like to have a table like this:

| PROJECT_ID |  TITLE | MONTH_1 | MONTH_2 | MONTH_3 | MONTH_4 |
---------------------------------------------------------------
|          1 | title1 |     100 |    2000 |     900 |     900 |
|          2 | title2 |     200 |     200 |     NULL|     NULL|
|          3 | title3 |     500 |    NULL |     NULL|     NULL|

Can I realize it only with JOIN and without subqueries?

Thanks!

@Close voters: The suggested duplicate is about an unknown number of columns and has dynamic SQL as an answer. That is no fit at all for this question. — Andomar, Aug 15 '14 at 08:13

score 3 · Answer 1 · answered Aug 15 '14 at 07:32

3

Turning rows into columns is called pivoting. One approach is to group by each project. The group will become a single row. Inside the group, you define one column per month. The column sums up the expenses for one particular month:

select  p.PROJECT_ID
,       p.TITLE
,       sum(case when m.month = 1 then m.expense end) as MONTH_1
,       sum(case when m.month = 2 then m.expense end) as MONTH_2
...
from    project p
join    monthly m 
on      p.PROJECT_ID = m.PROJECT_ID
group by 
        p.PROJECT_ID
,       p.TITLE

answered Aug 15 '14 at 07:32

Andomar

232,371
49
380
404

Thanks! But what if I have more Expense columns? – Yelnar Aug 15 '14 at 09:56
What do you mean? A column `Expense2` in the `monthly` table? Or multiple rows for one month in the `monthly` table? In the later case, this query should work fine. In the first case, replace `m.expense` with `m.expense1 + m.expense2`. – Andomar Aug 15 '14 at 10:00
I mean, what if I want to have columns like MONTH_1_EXPENSE_1, MONTH_1_EXPENSE_2, .., MONTH_4_EXPENSE_2? – Yelnar Aug 15 '14 at 10:03

score 2 · Answer 2 · answered Aug 15 '14 at 07:33

2

try like this

select p.PROJECT_ID,p.TITLE,
case when month=1 then EXPENSE end as Month_1,
case when month=2 then EXPENSE end as Month_2,
case when month=3 then EXPENSE end as Month_3,
case when month=4 then EXPENSE end as Month_4
from Project p inner join ProjectMonthly' pm on p.PROJECT_ID=pm.PROJECT_ID

answered Aug 15 '14 at 07:33

Sathish

4,419
4
30
59

2

+1 That would work if there is never more than one row per month per project (which seems likely) – Andomar Aug 15 '14 at 07:34

score 2 · Answer 3 · answered Aug 15 '14 at 07:38

You can do so by using case with max to get the desired result set,below query will only give the results for 4 months if you have more than 4 months then you have to write cases for all months with a max to pick greater value among the values for a month,i have used inner join so the projects that exists in ProjectMonthly only these projects will be returned if any project has no monthly data and you still want it to return the results the change inner join to left join

SELECT p.PROJECT_ID,p.TITLE,
MAX(CASE WHEN MONTH=1 THEN EXPENSE END) AS Month_1,
MAX(CASE WHEN MONTH=2 THEN EXPENSE END) AS Month_2,
MAX(CASE WHEN MONTH=3 THEN EXPENSE END) AS Month_3,
MAX(CASE WHEN MONTH=4 THEN EXPENSE END) AS Month_4
FROM Project p 
INNER JOIN ProjectMonthly pm 
ON p.PROJECT_ID=pm.PROJECT_ID
GROUP BY p.PROJECT_ID

`Fiddle Demo`

Thanks a lot. And what if I have two or more fields of expenses? And I would like to have table with columns like MONTH_1_EXPENSE_1, MONTH_1_EXPENSE_2 etc. — Yelnar, Aug 15 '14 at 09:49
@Francois then you can use same case with the expense column name like `MAX(CASE WHEN MONTH=1 THEN EXPENSE_1 END) AS Month_1_EXPENSE,MAX(CASE WHEN MONTH=1 THEN EXPENSE_2 END) AS Month_2_EXPENSE,` and so on as many expenses column u have — M Khalid Junaid, Aug 15 '14 at 16:18

JOIN TWO TABLES

3 Answers3

Fiddle Demo

`Fiddle Demo`