MMartin1 (12/17/2015)

---

Banbo Bird (12/14/2015)

---

Hi,how can I get result like this:

Create table T1

(

pid INT primary key identity(1,1),

name varchar(10),

amount int

)

Create table T2

(

sid INT primary key identity(1,1),

subnum int,

flag varchar(5),

pid int

)

insert into T1

select 'A',100 union

select 'B',200

insert into T2

select 20,'a1',1 union

select 30,'a2',1 union

select 50,'a3',1 union

select 100,'b1',2 union

select 80,'b2',2 union

select 20,'b3',2

T1 and t2 relation is one to many used "pid"

and the result like this:

pid name amount subnum flag

1 A 100 20 a1

0 0 0 30 a2

0 0 0 50 a3

2 B 200 100 b1

0 0 0 80 b2

0 0 0 20 b3

how can I do?

I just want to note that aside from the column <subnum> , all the rest look non additive. They are descriptive columns and in this case do no accurately represent the data when you mask items with zeros. I can see where you might be trying to hide private information in a report but in general I am curious why you are taking this approach.