DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Query Help

  1. #1
    Join Date
    Apr 2008
    Posts
    1

    Query Help

    I have table (udf) with the following columns and data:

    UserID Feildno Value
    1 1 Name
    1 2 Department
    1 3 SSN
    1 4 Cost Center


    I am trying to construct a query that would allow me to return data in value on separate columns

    if I use the following query I get these results

    Select userid,value from udf
    where fieldno = '1' or fieldno = '3'

    UserID Value
    1 Name
    1 SSN

    I need to return only 1 row of data such as

    UserID Value Value
    1 Name SSN

    Any Suggestions?

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    I think you are talking about a pivot query.
    This should probably give you what you want.
    fyi, I have not tested the it in any way.

    Code:
    SELECT main.userid, name.name, department.department, 
           ssn.ssn, costcenter.costcenter
      FROM ( SELECT DISTINCT userid
               FROM table ) main
     INNER JOIN
           ( SELECT userid, Feildno, Value Name
               FROM table ) name
        ON name.userid        = main.userid
       AND name.Feildno       = 1
     INNER JOIN
           ( SELECT userid, Feildno, Value department
               FROM table ) department
        ON department.userid  = main.userid
       AND department.Feildno = 2
     INNER JOIN
           ( SELECT userid, Feildno, Value ssn
               FROM table ) ssn
        ON ssn.userid         = main.userid
       AND ssn.Feildno        = 3
     INNER JOIN
           ( SELECT userid, Feildno, Value costcenter
               FROM table ) costcenter
        ON costcenter.userid  = main.userid
       AND costcenter.Feildno = 4
     ORDER BY main.userid;

  3. #3
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    I would just autojoin the source table like...
    Code:
    SQL> 
    SQL> create table udf
      2  (userid number,
      3  fieldno number,
      4  value varchar2(20))
      5  ;
    
    Table created.
    
    SQL> insert into udf values(1,1,'Name');
    
    1 row created.
    
    SQL> insert into udf values(1,2,'Department');
    
    1 row created.
    
    SQL> insert into udf values(1,3,'SSN');
    
    1 row created.
    
    SQL> insert into udf values(1,4,'Cost Center');
    
    1 row created.
    
    SQL> commit;
    
    Commit complete.
    
    SQL> select a.userid, a.value, b.value
      2  from   udf a,
      3         udf b
      4  where  a.userid = b.userid
      5  and    a.fieldno = 1
      6  and    b.fieldno = 3
      7  ;
    
        USERID VALUE                VALUE
    ---------- -------------------- --------------------
             1 Name                 SSN
    
    SQL>
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    Quote Originally Posted by PAVB
    I would just autojoin the source table like...
    Is that autojoin or cartesean product join??? How does Oracle know that you don't want a cartesean product?

  5. #5
    Join Date
    Jan 2007
    Posts
    231
    there is no need of
    a.userid = b.userid to avoid cartesian.

  6. #6
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Quote Originally Posted by gandolf989
    Is that autojoin or cartesean product join??? How does Oracle know that you don't want a cartesean product?
    I'm sorry for the confusion, I used the expression autojoin in the sense of joining a table with itself, my fault.

    That been said...
    Does my solution works? Yes
    Does it solves the problem? Yes
    Is it both elegant and easy do read? Yes

    In that case let me quote the former Chinese PM when he said "...I don't care if the cat is white or black provided the cat catches the mouse"
    Last edited by PAVB; 05-02-2008 at 07:55 AM.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  7. #7
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Quote Originally Posted by ams-jamali
    there is no need of
    a.userid = b.userid to avoid cartesian.
    Have you tested your idea? do it, it doesn't work.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  8. #8
    Join Date
    Jan 2007
    Posts
    231
    here is the code

    Code:
    SQL> select * from try;
    
        USERID    FIELDNO VALUE
    ---------- ---------- --------------------
             1          1 Name
             1          2 Department
             1          3 SSN
             1          4 Cost Center
    
    
    SQL> select a.userid,a.value,b.value from
      2  try a,
      3  try b
      4  where a.fieldno=1
      5  and b.fieldno=3;
    
        USERID VALUE                VALUE
    ---------- -------------------- --------------------
             1 Name                 SSN
    Thanks
    ams-jamali

  9. #9
    Join Date
    Jan 2007
    Posts
    231
    here the idea is your's.., gandolf989 Quoted so i told that..,

  10. #10
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Quote Originally Posted by ams-jamali
    here is the code
    Your code appears to be working just because of all rows in your table have userid=1, cant' you see that?
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Posting Permissions

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


Click Here to Expand Forum to Full Width