Multiple rows convert in to column and column should be count of rows in sql DB2

Question

I have two tables in below format:

Table 1:

R.no       name      type
--------------------------
1.          Apple    1
2.          Apple    2
3           Apple.   3
4.          Apple.   1
5           apple.   2

Table 2:

R.no     type.     S. Name
-------------------------------
1.         1.       Fresh
2.         2.       Bbbb
3.         3.       Cccc

My required output is

R. No.  Name.    Fresh.    Bbbb.     Cccc
-------------------------------------------
1.      Apple.   2.        2.        1

can anyone help on this?

I believe it suppose to be simple count of types and pivot the result — Veljko89
– Veljko89, Commented Sep 6, 2019 at 7:43

DarkRob · Accepted Answer · 2019-09-06 08:00:34Z

1

You may try this.

with cte as 
(
  select A.id, A.value, b.type, b.sname from A inner join B on A.code=B.type
)
 select value,
       sum(case when sname='Fresh' then 1 else 0 end) Fresh,
       sum(case when sname='Bbbb' then 1 else 0 end) Bbbb,
       sum(case when sname='Cccc' then 1 else 0 end) Cccc
from cte
group by value

Working fiddle for same is Fiddle.

answered Sep 6, 2019 at 8:00

DarkRob

3,8231 gold badge12 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Multiple rows convert in to column and column should be count of rows in sql DB2

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related