CODEWITHSUNDEEP
oracle-database
MotoIsMyMoto
MotoIsMyMoto

Reputation: 11

Formatting query result

Let's say I have one query that returns column1, column2. I'd like to be able to format the result to a desirable output in Oracle.

I want to mess up with strings that's why I'm asking that question ~~.

create table taTest(
column1 varchar(50),
column2 varchar(50)
)

this is the basic query that I want to format the result from :

select distinct(column1),column2 from taTest;


desired output(for each query result) : column1value(column2value)

I've tried something like this : 

select wm_concat(distinct(column1)||'('||column2||')') as result  from taTest;

But it seems like I'm not using wm_concat the right way.

Thanks for any input you might provide.

Upvotes: 1

Views: 51

Answers (1)

T.S.
T.S.

Reputation: 19330

This distinct(column1), column2 is one of the biggest fallacies that I've seen. Because this is same as 

select distinct column1, column2. . .

Hence, all you need is this 

select
    column1 || '('|| column2 || ')' as result
from
    taTest
group by
    column1, column2;

In this case group by and distinct perform same function

Going back to distinct(column1) - this is false function. The rule of SQL parsing is that anything within ( ) considered a single word\group. Hence (column1) is separated from distinct using () vs empty space -same thing. You can do 

select distinct (column1), (column2) . . -- same thing

Upvotes: 1

Related Questions