Pivot table from 4 different tables

Question

I have four tables with 2 columns each in that one column name common "Workflow" tb1

Workflow | progress ----------------------- export data | 3

t2

Workflow | closed ---------------------- import data | 4

t3

Workflow | Finished ---------------------- extra data | 2

t4

Workflow | notyet ---------------------- Oracle tags | 7

Now I am looking a result as follows,

Workflow | Progress | Closed | Finished | notyet | ------------------------------------------------------------- export data | 3 | 0 | 0 | 0 | ------------------------------------------------------------- import data | 0 | 4 | 0 | 0 | ------------------------------------------------------------- extra data | 0 | 0 | 2 | 0 | ------------------------------------------------------------- Oracle tags | 0 | 0 | 0 | 7 |

Any type id's colums for joining tables

Mohammad Shehroz
– Mohammad Shehroz

2019-01-20 10:54:44 +00:00
Commented Jan 20, 2019 at 10:54 — Mohammad Shehroz
– Mohammad Shehroz, Commented Jan 20, 2019 at 10:54

Derviş Kayımbaşıoğlu · Accepted Answer · 2019-01-20 13:42:27Z

The basic approach of doing this would be

select * from ( select workflow, progress, 0 as closed, 0 as finished, 0 as notyet from tb1 union all select workflow, 0, closed, 0,0 from tb2 union all select workflow, 0, 0, finished, 0 from tb3 union all select workflow, 0, 0, 0, notyet from tb4 ) t1

If you need grouped result then:

select workflow, sum(progress) as progress, sum(closed) as closed, sum(finished) as finished, sum(notyet) as notyet from ( select workflow, progress, 0 as closed, 0 as finished, 0 as notyet from tb1 union all select workflow, 0, closed, 0,0 from tb2 union all select workflow, 0, 0, finished, 0 from tb3 union all select workflow, 0, 0, 0, notyet from tb4 ) t1 group by workflow;

Collectives™ on Stack Overflow

Pivot table from 4 different tables

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related