Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2004
    Posts
    5

    Unanswered: Select with row value as column name

    I need to create a VIEW which contains columns naming from other tables' row value. How can I do this?
    for example:

    TABLE T1,

    Attribute Value
    SSN, 123456789
    FIRST_NAME, JOHN,
    LAST_NAME, SMITH

    VIEW
    SSN FIRST_NAME LAST_NAME
    123456789 JOHN SMITH
    .............
    ...
    etc

    Tien-Chih Wang

  2. #2
    Join Date
    Aug 2003
    Location
    Guwahati, India
    Posts
    33

    Can you explain !

    Hi ,

    Can you explain

    Say in dept_mst table

    Dept_id Dept_name Deleted

    1 Admin N
    2 HR N
    3 Finance N



    Do you want the view like

    1 2 3
    Admin HR Finance
    N N N


    regards,
    G J Shankar Nath
    IT Consultant, Vedswasti Services Pvt. Ltd
    Guwahati-781007
    India

  3. #3
    Join Date
    Apr 2004
    Location
    Toronto, Canada
    Posts
    249
    If you know the attribute names, then it is possible. If the attribute names are not fixed, then it is very difficult.

    Assuming that you know what attributes you are interested in, then here's how you'd do it.

    Create view v_x as
    select (select value from t1 where attribute = 'SSN'),
    (select value from t1 where attribute = 'FIRST_NAME'),
    (select value from t1 where attribute = 'LAST_NAME')
    from dual;

    Of course, this will have to be modified so that each sub-query returns exactly one row. There must be some way to identify the "row" that you are interested in.

    In general, attribute-value pairs as table design are not recommended because they cause such problems.

    Ravi

Posting Permissions

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