Results 1 to 10 of 10
  1. #1
    Join Date
    Jun 2008
    Posts
    5

    Unanswered: View by dynamically selecting columns?

    Hi all,
    I have I problem which I have no idea how to solve and would be very thankful for any suggestions.
    I have 2 tables:
    Table1: ProdNum(primary key), A,B,C,D
    Table2(contains different properties assigned to the products from table1): ID(primary key), ProdNum, Property
    The resulting view should contains ALL columns from Table1 + a column for each distinct property existing in Table2, with value 1 or 0 depending on whether this property has been to this product or not.
    Example:
    Table2:
    ID ProdNum Property
    1 1234 modern
    2 1234 youthful
    3 1235 modern
    4 1235 exotic
    Then in the view we would have:
    Prodnum modern youthful exotic
    1234____ 1 ____ 1 _____ 0
    1235_____1_____ 0_____ 1
    My question is how can i dymanically select columns for each distinct property in Table2?
    Thank you.
    P.s. "DB2 v9.5.0.808", "s071001", "NT3295" und FixPak "0"
    Win Server 2003 R2 sp1

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    This is an FAQ - have a look at "pivoting" or "transposing" tables.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Jun 2008
    Posts
    5
    Hi stolze,
    Thank you very much for the hint. I searched, but the problem is all proposed solutions either merge the properties into 1 column, or create an output with disticnt columns, but in the query it's assumed the total number of possible columns is known...which is not my case
    Is there a way to store the distinct properties in a cursor and then dynamically select....as property_i for each value in the cursor?
    Or should I somehow create a separate table which dynamically updates its number of columns?

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by farwla
    but in the query it's assumed the total number of possible columns is known...which is not my case
    Then you want to do something with SQL for which SQL was never designed for.

    The idea of relational database systems is that everything is a table. A table has a well-defined number of columns and data types for each column. Then you can apply predicates on tables, join tables, and project columns away. That's pretty much all there is...

    The "everything is a table" also applies to result sets, which are also just tables.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I couldn't understand why farwla(OP) stic to the view with arbitrary number of columns.
    Because.....
    1) Anyhow, DB2 have limits. Less than one thousand or five hundreds columns would be a practical limit.
    Some references from DB2 SQL Reference:
    - Maximum number of columns in a view(1): 5000
    (1) This maximum can be achieved using a join in the CREATE VIEW statement.
    Selecting from such a view is subject to the limit of most elements in a select list.

    - Maximum number of elements in a select list(7): 1012
    (7) For page size-specific values, see Table 35 on page 565

    (As Stolze wrote) The name of columns should be fixed at the time executing CREATE VIEW.

    If you want to name the columns to Property values,
    you need to recreate the view everytime Table2 was updated(insert/delete/update Property values).

    I guessed that you might want to make a query like this:
    Code:
    SELECT Prodnum
      FROM _view_
     WHERE modern = 1
       AND youthful = 1;
    It would be impossible without drop/create the _view_ everytime Table2 was updated.

    The query using GROUP BY and HAVING clause might be equivalent to the former query.
    (Replaced "xxxxxxx = 1" to "COUNT(CASE Property WHEN 'xxxxxxx' THEN 1 END) = 1".)
    Code:
    SELECT ProdNum
      FROM Table2
     GROUP BY
           ProdNum
    HAVING COUNT(CASE Property WHEN 'modern' THEN 1 END) = 1
       AND COUNT(CASE Property WHEN 'youthful' THEN 1 END) = 1;
    You can mix AND/OR and NOT("COUNT(...) = 0"), sameway as the former query.
    Last edited by tonkuma; 06-17-08 at 14:13.

  6. #6
    Join Date
    Jun 2008
    Posts
    5
    The problem is that records are added to the second table (with the properties), so everytime a new record is added I would have to check whether it contains a new poperty and create a new query.....which is actually not acceptable, it should work somehow automatically.
    The view is accessed by a Data Mining application and therefore i need the data presented in this way.

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Can't you list up possible Property values?

  8. #8
    Join Date
    Oct 2007
    Posts
    48
    Provided Answers: 3
    Try looking up recursive sql. I haven't played around with it too much, but you should be able to come up with something I would think.
    Dave

  9. #9
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by tonkuma
    Can't you list up possible Property values?
    Exactly. That's the only way: you have to know exactly how many columns you can possibly have, then define a view based on this information. If you add more property values, you have to re-create the view and make all applications aware of that change. For dynamic SQL applications, that may mean you don't have to do anything, for static SQL applications, you may have to change the application code.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  10. #10
    Join Date
    Jun 2008
    Posts
    5
    Thank you very much for the suggestions, I will try to get from the partners an estimation of the max possible number of properties.

Posting Permissions

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