Results 1 to 13 of 13
  1. #1
    Join Date
    Sep 2006
    Posts
    8

    Red face Unanswered: 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!
    Last edited by dbhoop; 12-15-06 at 07:13.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2006
    Posts
    8
    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.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


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

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    lkbrown, if the number of entries is variable, then this problem cannot be done with just sql

    which is probably why he was posting
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    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.)
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    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 ...)
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    that's mighty impressive, i like it

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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Sep 2006
    Posts
    8
    thanks a lot.
    i tried to get it work, even though i didnt get it completely. I need a little time for it.

  12. #12
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    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
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  13. #13
    Join Date
    Sep 2006
    Posts
    8
    So Sorry, that i didnt thanked you!
    Thanks a lot Peter!!!! This was helping me out!!!!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •