query, special transpose and merge of data

hi all,

i'm a newbie with a big problem :). I want to create a query, which creates data in a merged and transposed way.

i have 3 tables, but only two of them are interesting for me now.

Table0:
F_id, prop1, prop2 ...
-------
10, x, y
20, v, d

Table1 (m:n Table, connecting Table0 with Table 2):
F_id, V_id
----
10, a
10, b
20, a

Table2:
V_id
--
a
b
c
d

the sql should create this result:
SQL Result:
F_id, a, b, c, d
-----
10, 1, 1, 0, 0
20, 1, 0, 0, 0

the tupels of Tabl2 with V_id should be transposed as columns names of the sql result.
Then every entry in the Table1 (m:n-Table) should insert a '1' in the column of the corresponding V_Id, otherwise if there is no connection between Table0 and Table2 in the m:n-Table, then there is a '0' to be inserted.

In the moment i have no clue, i read a lot about transposing and crosstab things, but that was no help for my special problem.

I appreciate any help.
Thanks!select T0.F_id
, sum(case when T1.V_id = a
then 1 else 0 end) as a
, sum(case when T1.V_id = b
then 1 else 0 end) as b
, sum(case when T1.V_id = c
then 1 else 0 end) as c
, sum(case when T1.V_id = d
then 1 else 0 end) as d
from Table0 as T0
inner
join Table1 as T1
on T1.F_id = T0.F_id
group
by T0.F_id|||Thanks. But the problem is that the Table1-entries are dynamic or the number of entries are variable.

Maybe i correct sth; its not really important to get all of these Table1-entries, but all entries from the m:n Table should be inserted.|||thanks, but you should have mentioned that in your initial post

would have saved me wasting my time writing sql that you can't use

:)|||Besides, this looks like a clasical "homework" assignment -- what have you done yourself to solve the problem? :mad:|||lkbrown, if the number of entries is variable, then this problem cannot be done with just sql

which is probably why he was posting|||if the number of entries is variable, then this problem cannot be done with just sql
It can, by using recursive SQL.
(Of course, one cannot return a "variable" number of columns, but a column can be returned which contains a variable amount of concatenations of expressions.)|||It can, by using recursive SQL.oh, please do show an example

and please make sure it is standard sql, not db2 or something proprietary

:)|||oh, please do show an example

and please make sure it is standard sql, not db2 or something proprietary
WITH T(F_id, aux, V_id) AS
( SELECT Table0.F_id,
MIN(Table1.V_id),
COALESCE(T.V_id, '') || ', ' || MIN(T1.V_id)
FROM Table0 AS T0 LEFT OUTER JOIN T ON T0.F_id = T.F_id
INNER JOIN Table1 AS T1 ON T0.F_id = T1.F_id
WHERE T.aux IS NULL or T1.V_id > T.aux
GROUP BY Table0.F_id
)
SELECT F_id, V_id
FROM T

Didn't test it, so there could be some minor tweaks ...)|||that's mighty impressive, i like it

but frankly, i get lost when i try to understand what it's doing

:)|||thanks a lot.
i tried to get it work, even though i didnt get it completely. I need a little time for it.|||I've tested the following and it works:
create table T0 ( f int ) ;
create table T1 ( f int , v char(1) ) ;
insert into T0(f) values(10) union all values(20) ;
insert into T1(f,v) values(10,'a') union all values(10,'b') ;
insert into T1(f,v) values(20,'b') union values(20,'c') union values(20,'d');

with T (f, v, aux) AS
(SELECT f, CAST('' AS varchar(255)), CAST(null AS varchar(255)) FROM T0
UNION ALL
SELECT T.f, T.v||', '||coalesce(T1.v, ''), coalesce(T1.v, '')
FROM T, T1
WHERE T.f = T1.f AND coalesce(T.aux, '') < T1.v
)
SELECT f, substr(v, 3)
FROM T AS Tx
WHERE length(v) = (SELECT max(length(v))
FROM T
WHERE T.f = Tx.f)
Quick explanation:
The "recursive" table T is built up as follows:
- First it's given all rows of table T0, i.e.
10, '', ''
20, '', ''
- Then the join of this table with T1 is added. The result is
10, '', ''
20, '', ''
10, ', a', 'a'
10, ', b', 'b'
20, ', b', 'b'
20, ', c', 'c'
20, ', d', 'd'
- This last step is iterated, but such that only rows of T and T1 are considered to be joined if T.aux (last column) is strictly smaller than T1.v .
Hence the following rows are added to T in step 3:
10, ', a, b', 'b'
20, ', b, c', 'c'
20, ', b, d', 'd'
20, ', c, d', 'd'
Finally (for the small tables used here) the row
20, ', b, c, d', 'd'
is added.
With this table T, the actual query (SELECT f, substr(v, 3) FROM T) is executed. The "substring" removes the leading ", " while the "WHERE" condition only keeps the longest strings in v, per f, i.e. the result is:
t | v
-- + ---
10 | a, b
20 | b, c, d|||So Sorry, that i didnt thanked you!
Thanks a lot Peter!!!! This was helping me out!!!!!!