Parent Child count- so complicated!

Hi Guys

I have a dataset where there are parent and child products.

Each parent has got a unique code(P1,p2 etc) and a link code (L1) on their account

and each child of the parent has got a seperate code (C12) but they share the same account no.

I want to count the parent and children

Here is the ddl with the desired output.

Pls help!!

create table #Something

(

Line_no int,

code varchar(5),

AccountNo char(5)

)



insert #Something

select 12311, 'P1c', 'Ac115' union all

select 12311, 'L1', 'Ac115' union all

select 123, 'C1', 'Ac115' union all

select 1222, 'C1', 'Ac115' union all

select 1243, 'C1', 'Ac115' union all

select 433, 'P1a', 'Ac111' union all

select 433, 'L1', 'Ac111' union all

select 4331, 'C1', 'Ac111' union all

select 543, 'C1', 'Ac222' union all

select 544, 'C1', 'Ac222' union all

select 4322, 'P1b', 'Ac222' union all

select 4322, 'L1', 'Ac222' union all



select 8766 'P1d' , 'Ab111' union all

select 8766 'L1' , 'Ab111' union all

select 8767 'C1', 'Ab111' union all

select 8789 'P1d', 'Ab119' union all

select 8766 'L1', 'Ab119' union all

select 876654 'C1', 'Ab119' union all

select 876655 'C1', 'Ab119' union all

select 876698 'P1a', 'Ab117' union all

select 876698 'L1', 'Ab117' union all

select 987 'C1', 'Ab117' union all

select 555444 'P1d' 'Xcv' union all

select 555444 'L1' 'Xcv' union all

select 6754 'C1' 'Xcv' union all



select * from #Something



drop table #Something





Desired output is:



[Parent code] [Parent line Count] [Child line Count]



P1c 1 3

P1a 2 2

P1b 1 2

P1d 3 4

Thanks