CODEWITHSUNDEEP
oracle-databaseoracle11goracle10g
LearnUser
LearnUser

Reputation: 1

How to write oracle query to get the data of one column values

Need help to write query

I have table with one column, with values A, B, C & D.

How to write a query to get below results: AB, AC, AD, BA, BC, BD, ...

Any body help on this. Thanks in advance

Upvotes: 0

Views: 190

Answers (3)

Eddy
Eddy

Reputation: 1

Cool answers. This is my version, combinations of both answers:

WITH x AS (
  SELECT 'A' AS col FROM dual
  UNION ALL
  SELECT 'B' AS col FROM dual
  UNION ALL 
  SELECT 'C' AS col FROM dual
  UNION ALL
  SELECT 'D' AS col FROM dual
)

SELECT x1.col || x2.col AS xx
FROM x x1 JOIN x x2 ON x1.col <> x2.col

Upvotes: 0

Samuel Renold
Samuel Renold

Reputation: 302

This SQL should do the trick:

SELECT T1.COL || T2.COL
FROM MY_TABLE T1, MY_TABLE T2
WHERE T1.COL != T2.COL;

Here, MY_TABLE is your table with column COL.

Upvotes: 1

tbone
tbone

Reputation: 15493

Try:

with x as (
    select 'A' as col from dual
    union all
    select 'B' as col from dual
    union all
    select 'C' as col from dual
    union all
    select 'D' as col from dual
)
select *
from x join x x2 on x.col != x2.col

Output:

COL COL_1
"A" "B"
"A" "C"
"A" "D"
"B" "A"
"B" "C"
"B" "D"
"C" "A"
"C" "B"
"C" "D"
"D" "A"
"D" "B"
"D" "C"

Upvotes: 2

Related Questions