query, special transpose and merge of data

dbhoop · #1 (**permalink**) 12-15-06, 05:00

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!

r937 · #2 (**permalink**) 12-15-06, 06:11

Code:

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

dbhoop · #3 (**permalink**) 12-15-06, 06:48

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.

r937 · #4 (**permalink**) 12-15-06, 06:53

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

LKBrwn_DBA · #5 (**permalink**) 12-15-06, 08:51

Besides, this looks like a clasical "homework" assignment -- what have you done yourself to solve the problem?

r937 · #6 (**permalink**) 12-15-06, 09:00

lkbrown, if the number of entries is variable, then this problem cannot be done with just sql

which is probably why he was posting

Peter.Vanroose · #7 (**permalink**) 12-15-06, 12:14

Quote:

Originally Posted by r937

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.)

r937 · #8 (**permalink**) 12-15-06, 12:16

Quote:

Originally Posted by Peter.Vanroose

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

Peter.Vanroose · #9 (**permalink**) 12-15-06, 12:46

Quote:

Originally Posted by r937

oh, please do show an example

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

Code:

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 ...)

r937 · #10 (**permalink**) 12-15-06, 15:47

that's mighty impressive, i like it

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

dbhoop · #11 (**permalink**) 12-16-06, 10:36

thanks a lot.
i tried to get it work, even though i didnt get it completely. I need a little time for it.

Peter.Vanroose · #12 (**permalink**) 12-17-06, 15:15

I've tested the following and it works:

Code:

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:

Code:

t	|	v
--	+	-------
10	|	a, b
20	|	b, c, d

dbhoop · #13 (**permalink**) 09-12-07, 10:37

So Sorry, that i didnt thanked you!
Thanks a lot Peter!!!! This was helping me out!!!!!!

Internet Services

Web Development

Digital Marketing

Consumer Tech