-
Notifications
You must be signed in to change notification settings - Fork 0
/
concatenation.sql
65 lines (57 loc) · 1.32 KB
/
concatenation.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
drop table tq84_b;
drop table tq84_a;
create table tq84_a (
id integer primary key,
tx varchar(10)
);
insert into tq84_a values (1, 'one' );
insert into tq84_a values (2, 'two' );
insert into tq84_a values (3, 'three');
create table tq84_b (
id_a integer not null references tq84_a,
itm varchar(10)
);
insert into tq84_b values (1, 'foo' );
insert into tq84_b values (1, 'bar' );
insert into tq84_b values (1, 'baz' );
insert into tq84_b values (2, 'eggs');
insert into tq84_b values (2, 'why' );
--
-- Use a subquery and xml for to concatenate the children
-- for a particular row of the parent table;
--
select distinct
a.tx,
(select ' - ' + b.itm
from tq84_b b
where b.id_a = a.id
for xml path ('')) concat_itm
from
tq84_a a join
tq84_b b on a.id = b.id_a;
--
-- Unfortunately, the concatenation is prefixed with a ` - `:
--
-- two - eggs - why
-- one - foo - bar - baz
--
--
-- Remove the first ' - ' of the concatenation with stuff:
--
select distinct
a.tx,
stuff (
(select ' - ' + b.itm
from tq84_b b
where b.id_a = a.id
for xml path ('')) ,
1, 3, '' -- Replace characters 1 through 3 with nothing (that is: remove them)
) concat_itm
from
tq84_a a join
tq84_b b on a.id = b.id_a;
--
--
--
-- one foo - bar - baz
-- two eggs - why