SQL for Smarties | SQL Programming Style | Trees and Hierarchies in SQL | SQL Puzzles and Answers | Data and Databases


Thursday, June 15, 2006

Results in Parallel columns

SQL Apprentice Question
I need to place the results of two different queries in the same result
table parallel to each other.
So if the result of the first query is


1 12
2 34
3 45


and the second query is


1 34
2 44
3 98


the results should be displayed as


1 12 34
2 34 44
3 45 98


If a union is done for both the queries , we get the results in rows.
How can the above be done.

Celko Answers

>>I need to place the results of two different queries in the same result table parallel to each other. <<


This is not a table; the rows of a table model elements of a set of the
same kind of thing. What you want is a display kludge to show, say,
automobiles and squid as if they were the same kind of things.

Here is your kludge, since people often gripe that I do not post
the bad code the OP wants:


SELECT *, ROW_NUMBER() OVER(ORDER BY duh) AS lft_nbr
FROM Foo
FULL OUTER JOIN
SELECT *, ROW_NUMBER() OVER(ORDER BY doh) AS rgt_nbr
FROM Bar
ON Foo.lft_nbr = Bar.rgt_nbr;


The right way is handle display issues in the applications and front
ends, not the RDBMS.

No comments: