December 14, 2015 at 10:42 pm
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?
December 15, 2015 at 1:31 am
Something like the following
;with cte as
(
select
row_number() over (partition by t1.pid order by t1.pid, t2.flag) as RN,
t1.pid,
t1.name,
t1.amount,
t2.subnum,
t2.flag
from
t1
inner join
t2
on t1.pid = t2.pid
)
select
case when rn = 1 then pid else 0 end as pid,
case when rn = 1 then name else '0' end as name,
case when rn = 1 then amount else '0' end as amount,
subnum,
flag
from
cte
Do some google'ing on common table expressions and window functions to understand the concepts in use, anything you struggle with just post back.
December 17, 2015 at 12:31 pm
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.
----------------------------------------------------
December 17, 2015 at 11:45 pm
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.
I use it in a report .
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply