SQL trivia

[ad]

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

[lesstjm]

That is a lot harder than it looks.

[lesstjm]

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

[chrisrlong]

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