Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2003
    Posts
    1

    Unanswered: output the results of a select statement into two columns

    I want to output the results of a select statement into two columns. Half of all records will go in the first column, remaining half will go into the second column.

    Like this select statement will return one column "userid".

    SELECT userid
    FROM grouptable

    userid
    --------
    89
    90
    34
    23
    67
    33

    But I want to output this userid column into two columns - userid1 and userid2, where userid1 will have half the results and userid2 will have the remaining half of the results.

    userid1 userid2
    -------- ---------
    89 23
    90 67
    34 33

    ( The number of rows returned will always be even, so they can be divided into two columns.)

    Any idea for a sql script is much appreciated.

    Thanks.

  2. #2
    Join Date
    Oct 2003
    Location
    Dallas
    Posts
    76

    ...

    you could
    1)select the top 50 percent userid into temptable order by userid
    2)select the top 50 percent userid into temptable order by userid desc

    That way you have to top and bottom 50 percent in two temp tables then just merge the two columns from the two temp tables.

    just an idea

  3. #3
    Join Date
    Aug 2001
    Posts
    66

    Re: output the results of a select statement into two columns

    Are you on Oracle? Oracle Analytic functions provide an easy way to answer this question...

    Oracle8i Enterprise Edition Release 8.1.7.2.0 - Production
    With the Partitioning option
    JServer Release 8.1.7.2.0 - Production

    SQL> CREATE TABLE users (user_id NUMBER (2));

    Table created.

    SQL> INSERT INTO users VALUES (89);

    1 row created.

    SQL> INSERT INTO users VALUES (90);

    1 row created.

    SQL> INSERT INTO users VALUES (34);

    1 row created.

    SQL> INSERT INTO users VALUES (23);

    1 row created.

    SQL> INSERT INTO users VALUES (67);

    1 row created.

    SQL> INSERT INTO users VALUES (33);

    1 row created.

    SQL> COMMIT;

    Commit complete.

    SQL> SELECT user_id1, user_id2
    2 FROM (SELECT user_id user_id1,
    3 LEAD (user_id) OVER (
    4 ORDER BY user_id) user_id2,
    5 ROW_NUMBER () OVER (
    6 ORDER BY user_id) row_num
    7 FROM users)
    8 WHERE MOD (row_num, 2) = 1
    9 /

    USER_ID1 USER_ID2
    --------- ---------
    23 33
    34 67
    89 90

    SQL>
    Padderz
    SYSOP, Quest Software / RevealNet Labs PL/SQL Pipeline

Posting Permissions

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