Skip to Main Content
  • Questions

  • How to create a list of distinct column values

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Martin.

Asked: August 25, 2004 - 8:45 am UTC

Last updated: August 26, 2004 - 7:56 am UTC

Version: 9.2.0

Viewed 1000+ times

You Asked

Tom,

Consider this very much simplified example:

SQL> create table t1 (code varchar2(1));

Table created.

SQL> insert into t1 values('A');

1 row created.

SQL> insert into t1 values('B');

1 row created.

SQL> insert into t1 values('C');

1 row created.

SQL> insert into t1 values('A');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> select * from t1;

COD
---
A
B
C
A

I want to build a query that returns:

COD CODELIST
--- ----------------
A 'A','B','C'
B 'A','B','C'
C 'A','B','C'
A 'A','B','C'

So basically codelist returns all distinct codes in the table.
Is there an easy way to perform this?

Thanks in advance,

Martin

and Tom said...

search this site for stragg.


1* select code, stragg(code) over () allcode from t1
ops$tkyte@ORA9IR2> /

C ALLCODE
- ------------------------------
A A,B,C,A
B A,B,C,A
C A,B,C,A
A A,B,C,A


Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Great!

Martin, August 26, 2004 - 7:56 am UTC

Thanks Tom,

Very usefull as usual



The original question was asking for a list of "DISTINCT" column values

Frank Zhou, January 17, 2006 - 3:58 pm UTC 

Hi Tom,

       The original question was asking for a list of "distinct" column values,
but there was duplicate "A" ( "ALLCODE" : A,B,C,A )  in your answer.

How about this query?

SQL> SELECT code, 
(SELECT  RTRIM(REVERSE(MAX( SYS_CONNECT_BY_PATH (code, ',') )) , ',')
  2         FROM  T1
  3         CONNECT BY code < PRIOR code ) allcode
  4  FROM T1;

CODE  ALLCODE
----- --------------------
A     A,B,C
B     A,B,C
C     A,B,C
A     A,B,C

If the user doesn't want dupliate rows in the result set then use this query:

SQL> SELECT DISTINCT code, RTRIM(REVERSE(MAX(str) over ( )) , ',') allcode
  2  FROM
  3  (
  4   SELECT code,  SYS_CONNECT_BY_PATH (code, ',') str
  5         FROM  T1
  6         CONNECT BY code < PRIOR code
  7  )      ;

CODE  ALLCODE
----- --------------------
A     A,B,C
B     A,B,C
C     A,B,C

SQL> spool off


Thanks,

Frank

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.