SQL trivia
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: SQL trivia

  1. #1
    Join Date
    Dec 2000
    Posts
    28

    SQL question

    In a table, there are 3 rows and 2 columns. I need to retrieve all the data, but instead of getting 3 rows of 2 columns each, I need to get one row of 6columns. Need to do that only in SQL !

    for example,

    EMPLOYEE table

    NAME AGE
    ABC 28
    ZYA 38
    XYZ 48


    Now, how to get a single row from this table :

    ABC 28 ZYA 38 XYZ 48

    btw, i don't know the answer.

    Any idea ?

    Thanks,
    AD

  2. #2
    Join Date
    Jan 2001
    Posts
    515

    Unhappy ??

    That is a lot harder than it looks.

  3. #3
    Join Date
    Jan 2001
    Posts
    515

    Question

    Is there always going to be three rows with two columns or will it vary?

  4. #4
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    As alluded to, it is critical to know the number of columns that will be necessary in the final result. Columns cannot be dynamically 'created' in a SQL statement.

    IF the result set is 'fixed', then the following will work:

    SELECT
    ---MAX(DECODE ( ROWNUM,---1,---E.NAME, NULL))---AS ENAME1,
    ---MAX(DECODE ( ROWNUM,---1,---E.AGE---, NULL))---AS EAGE1---,
    ---MAX(DECODE ( ROWNUM,---2,---E.NAME, NULL))---AS ENAME2,
    ---MAX(DECODE ( ROWNUM,---2,---E.AGE---, NULL))---AS EAGE2---,
    ---MAX(DECODE ( ROWNUM,---3,---E.NAME, NULL))---AS ENAME3,
    ---MAX(DECODE ( ROWNUM,---3,---E.AGE---, NULL))---AS EAGE3---
    FROM
    ---EMPLOYEE---E
    WHERE
    ---ROWNUM <= 3

    Of course, you could add more columns, to some arbitray max to handle an arbitrarily maximum number of rows. If the number of rows can be large, however, you would be much better off dynamically building the above statement in PL/SQL based on the number of rows expected.

    HTH,

    - Chris

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