Display results in Columns

[datamart]

Suppose I have a table with only one column. When I query the table
SELECT COL1 FROM TABLE1;
This gives me the result
COL1
-------
VAL1
VAL2
VAL3
VAL4
...

But my requirement is to have the display in the format

VAL1 VAL2 VAL3 VAL4 ...

Is there a way to achieve the above?

Regards.

 

[ddiamond]

Is this going to me a manual process or an automated one? If it is manual, you could always load your results into excel, copy the column, and then paste special with the transpose option. If you need this to be done strictly with sql, then you may need to write a stored procedure using pl/sql. I am assuming that the number of values is not known in advance.

 

[datamart]

I am sorry to have missed an important point here. I cannot use a pl/sql. Instead I should be using normal SQL. Is it still possible?

 

[oradba101]

Hi, datamart

Is there any particular reason you can not use PL/SQL? I have a procedure I wrote that does this.

Regards,



William Chadbourne
Oracle DBA

 

[ddiamond]

I am not aware of anyway to do what you want with a static sql statement. If there is anyway you can use pl/sql, then I think William has the best solution for you. If not, then you will probably have to write a program to do it.

 

[ddiamond]

William,

You may want to post you procedure in the oracle FAQ section.

- Dan

 

[SantaMufasa]

I am sorry to have missed an important point here...

Yes, not being able to use PL/SQL was an important, missing point. But there are several others, as well:

1) Will there always be just a single row of output (as you suggest in your original post)?
2) If there are more than a single row of output, upon what condition will the rows "break"?
3) Will there always be a variable number of "columns" deriving from a variable number of rows?
4) Might there be "hundreds" of columns?...What are the maximum and minimum number of "columns" which you might encounter?
5) What "business" restriction causes the need to view the data horizontally instead of it more "native" vertical appearance?
6) What is there about your technical environment that precludes you from using a tool such as PL/SQL? What "other" restrictions affect possible solutions that we might suggest?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[datamart]

Santa,
Your questions are valid. But basically, there is no business requirement to achieve this. My friend was asked this question in an interview and we were wondering if this is possible.
Assume that the table is a restricted one with finite number of rows - we can assume it to be 10. Since this is test case scenario, we can assume no out-of-the-way conditions.

Regards

 

[SantaMufasa]

Datamart,

For a scenario as restricted/specific as you mention, you can go horizontal (from vertical) with the following technique:

SQL> select * from x;

 ID COL1
--- ------
102 Val1
102 Val2
102 Val3
102 Val4
102 Val5
102 Val6
102 Val7
102 Val8
102 Val9
102 Val10

10 rows selected.

col a format a5
col c1 like a
col c2 like a
col c3 like a
col c4 like a
col c5 like a
col c6 like a
col c7 like a
col c8 like a
col c9 like a
col c10 like a
select min(c1)c1,min(c2)c2,min(c3)c3,min(c4)c4,min(c5)c5
           ,min(c6)c6,min(c7)c7,min(c8)c8,min(c9)c9,min(c10)c10
from (
select col1 c1,null c2, null c3, null c4, null c5, null c6, null c7
          ,null c8, null c9, null c10
        from (select rownum rn, id, col1 from x) where rn = 1
 union 
select null c1,col1 c2, null c3, null c4, null c5, null c6, null c7
          ,null c8, null c9, null c10
        from (select rownum rn, id, col1 from x) where rn = 2
 union
select null c1,null c2, col1 c3, null c4, null c5, null c6, null c7
          ,null c8, null c9, null c10
        from (select rownum rn, id, col1 from x) where rn = 3
 union
select null c1,null c2, null c3, col1 c4, null c5, null c6, null c7
          ,null c8, null c9, null c10
        from (select rownum rn, id, col1 from x) where rn = 4
 union
select null c1,null c2, null c3, null c4, col1 c5, null c6, null c7
          ,null c8, null c9, null c10
        from (select rownum rn, id, col1 from x) where rn = 5
 union
select null c1,null c2, null c3, null c4, null c5, col1 c6, null c7
          ,null c8, null c9, null c10
        from (select rownum rn, id, col1 from x) where rn = 6
 union
select null c1,null c2, null c3, null c4, null c5, null c6, col1 c7
          ,null c8, null c9, null c10
        from (select rownum rn, id, col1 from x) where rn = 7
 union
select null c1,null c2, null c3, null c4, null c5, null c6, null c7
          ,col1 c8, null c9, null c10
        from (select rownum rn, id, col1 from x) where rn = 8
 union
select null c1,null c2, null c3, null c4, null c5, null c6, null c7
          ,null c8, col1 c9, null c10
        from (select rownum rn, id, col1 from x) where rn = 9
 union
select null c1,null c2, null c3, null c4, null c5, null c6, null c7
          ,null c8, null c9, col1 c10
        from (select rownum rn, id, col1 from x) where rn = 10
 );

C1    C2    C3    C4    C5    C6    C7    C8    C9    C10
----- ----- ----- ----- ----- ----- ----- ----- ----- -----
Val1  Val2  Val3  Val4  Val5  Val6  Val7  Val8  Val9  Val10

Although I didn't need to refer to the "ID" column in this case, I included it as a hook that you might extrapolate the code to handle virtually unlimited IDs whose (up-to-10) values you wanted to display (i.e., be able to "BREAK" on differing IDs). Let us know if this exemplifies a satisfactory resolution for your question.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[datamart]

Hi,
One of my friends was able to get this through in case of a fixed set of rows..
Here is the way you can do it.

create table temp_trans(
col1 varchar2(10)
)

Suppose you insert 3 rows in the table--- then,

select max(decode(rownum, 1, col1, null)),
max(decode(rownum, 2, col1, null)),
max(decode(rownum, 3, col1, null))
from temp_trans

The above sql should give you the values in a transpose form.
We are unable however to work out a logic in case of unknown number of rows in the table.
Any ideas.

Regards

 

[oradba101]

Good morning,

This is part of the hideous PL/SQL procedure I use. It may not be pretty but it works and it is fast.

Every couple of days, I have to submit a batch to the State of Maine for select mental health clients. We store the data in vertical format but the state wants it horizontally.

------------------------------------------------------------
-- This procedure parses the client's enrollment status from -- a vertical record layout to a horizontal one.

PROCEDURE AE_STATUS
------------------------------------------------------------
IS
BEGIN
DECLARE
d_client_id trclqsca.client_id%TYPE ;
d_trt_plan_id trclqsca.trt_plan_id%TYPE ;

v_client_id trclqsca.client_id%TYPE ;
v_trt_plan_id trclqsca.trt_plan_id%TYPE ;
v_service_under trclqsca.service_under%TYPE ;

CURSOR client_insert IS
SELECT DISTINCT a.client_id,
a.trt_plan_id
FROM trclqsca a,
trclpage b
WHERE a.trt_plan_id = b.trt_plan_id
AND b.page_id = 'ADENROLL06'
AND a.trt_plan_id NOT IN (SELECT trt_plan_id
FROM ae_history)
ORDER BY a.trt_plan_id ;

CURSOR service IS
SELECT client_id,
trt_plan_id,
service_under
FROM trclqsca ;

BEGIN
EXECUTE IMMEDIATE('TRUNCATE TABLE status_parse') ;

OPEN client_insert ;

-- This loop inserts the client_id and trt_plan_ids.
LOOP
FETCH client_insert INTO d_client_id, d_trt_plan_id ;
EXIT WHEN client_insert%NOTFOUND ;

INSERT INTO status_parse(client_id, trt_plan_id)
VALUES(d_client_id, d_trt_plan_id);
END LOOP ;

CLOSE client_insert ;
OPEN service ;

-- This loop inserts the status or type of enrollment -- that is being sent to the state.
LOOP
FETCH service INTO v_client_id, v_trt_plan_id, v_service_under ;
EXIT WHEN service%NOTFOUND ;

UPDATE status_parse
SET service_under_n = v_service_under
WHERE v_trt_plan_id = status_parse.trt_plan_id
AND v_service_under = 'N';

UPDATE status_parse
SET service_under_c = v_service_under
WHERE v_trt_plan_id = status_parse.trt_plan_id
AND v_service_under = 'C' ;

UPDATE status_parse
SET service_under_u = v_service_under
WHERE v_trt_plan_id = status_parse.trt_plan_id
AND v_service_under = 'U' ;

END LOOP ;

CLOSE service ;
COMMIT ;
END ;
END ae_status ;

The first loop inserts the client's ID number and the Treatemnt plan or medical record number. The second loop changes the data to a horizontal layout. I have a permanent table called "status parse" which is formatted for final vertical layout and is also used for for other reporting.

Regards,



William Chadbourne
Oracle DBA