output the results of a select statement into two columns

  • Thread starter Thread starter Calvin
  • Start date Start date
C

Calvin

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.
 
Dear Calvin:

You can easily do this in a report. But I'm having trouble seeing how
doing this in a query makes any sense.

Anyway, for what it's worth, I've never seen a way to do this.
Queries and databases are very organized things. What you propose
reduces the level of organization. So, it's my guess that you may not
be able to do this for the simple reason that the tools available for
queries generally aren't designed to reduce the level of organization.

OK, so if you MUST, I expect you can do this if there is a way to sort
the rows. You could sort them ascending and take the top 50%. Sort
them descending and take the top 50% again.

Also, add a column ranking each row in the forward sort order. Join
the two halves on the ranking column, with a blank value opposite the
actual values. Looks something like:

1 89 <blank>
1 <blank> 23
2 90 <blank>
2 <blank> 67
3 34 <blank>
3 <blank> 33

Use the MAX() aggregate when putting them together and you can get:

1 89 23
2 90 67
3 34 33

I think that would work, but you must have some unique sort that puts
them in the order you suggested: 89 / 90 / 34 / 23 / 67 / 33.

Then put those two halves together in a union query, making

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.
Click to expand...

Tom Ellison
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top