MY query returns this output :

A || B || C
== || == || ==

BK_1 || TE || ID
BK_1 || TE || BD


I want to display as :

A || B || C
== || === || ===

BK_1 || TE || ID,BD


Please guide . Is is possible using the coalesce ? Help with an example sql please

AFAIK, basically there are two ways.
1) Use recursive common table expression.
2) Use string aggregate function(LISTAGG, XMLGROUP or XMLAGG depending on your DB2 version).

If the max number of duplicated rows for each value of (A, B) was limited to two,
you can use an expression like...
MIN(c) || COALESCE(',' || NULLIF( MAX(c) , MIN(c) ) , '')

The max number of duplication of (A,B) is not limited to 2.
What other option can we use here please.

SAMPLE DATA :

There are 3 indexes on a table T1 :

IXBO has columns ( A1 ASC , B1 ASC ) ;

IXCO has columns( B1 ASC , A1 ASC ) ;

IXDO ( C1 ASC ) ;

Expected Output :

T_name || Col_name
------- ---------

T1 || A1 ASC , B1 ASC
T1 || B1 ASC , A1 ASC
T1 || C1 ASC

However when i run a simple query the out put i get is :

T_name || Col_name
------- || --------
IXBO || A1 ASC
IXBO || B1 ASC
IXCO || B1 ASC
IXCO || A1 ASC
IXDO || C1 ASC

Desired Output :

T_name || Col_name
------- || --------
IXBO || A1 ASC , B1 ASC
IXCO || B1 ASC, A1 ASC
IXDO || C1 ASC

Using : MIN(sk.column_name || ' '|| case sk.ordering when 'A' then 'ASC' else 'DESC' end) || COALESCE(',' || NULLIF( MAX(sk.column_name || ' '||case sk.ordering when 'A' then 'ASC' else 'DESC' end) , min(sk.column_name ||' '|| case sk.ordering when 'A' then 'ASC' else 'DESC' end) ) , '')

OUTPUT IS :

T_name || Col_name
------- || --------
IXBO || A1 ASC , B1 ASC
IXCO || A1 ASC, B1 ASC
IXDO || C1 ASC

The col_name for IXCO should be B1 ASC, A1 ASC.

The max number of duplication of (A,B) is not limited to 2.
What other option can we use here please.

There is no implicit sequence of rows in basic rule of relational DB including DB2.
If you want to gurantee the sequence of rows, you shoud add a column for the sequence.
Actually, DB2 catalog views indexcoluse and keycoluse include a column colseq for position of the column in the index(or key).

Anyhow, my sample expression is for at most two rows duplication.
So, you should not stick to the expression and you should consider recursive common table expression or string aggregate function.

please try to help with an example sql....

I lost interest in writing simple examples of string aggregation(or concatenation of strings in rows).

You can find examples of XMLGROUP and LISTAGG in last two post in this thread.
How to get change of IP in middle of session

I think that you can find examples of recursive common table expression by searching this forum.

The DB2 is V5 . So XML functions not compatible.
The earlier sample query that u provided works just fine.
However the max number of duplication of (A, B) is not limited to two.
I have provided sample data in previous post.
Kindly provide a small sample please, will be grateful to you.

Perhaps my poor English capability led you to misunderstanding.
I corrected my sentence to
"If the max number of duplicated rows for each value of (A, B) was limited to two, ..."
(Is it useful to clarify my intention?)

So, your sample was not sufficient.

By the way, I remembered I answerd your another question.
Help With SQL
In that thread, I provided examples of recursive common table expression in Example 1-2, Example 1-3 and Example 1-3'.

I was bored(in Japanese "Tsumanne" like the title of album by Shinsei Kamattechan) at coding simple example of recursive CTE.

Yes your intention is very clear to me.
The query is excellent works perfect where A,B are limited to 2.
But when an index is based on 3 columns the query gives incorrect output.


SAMPLE DATA :

There are 3 indexes on a table T1 :

IXBO has columns ( A1 ASC , B1 ASC ) ;

IXCO has columns( B1 ASC , A1 ASC ) ;

IXDO ( C1 ASC ) ;

There are 2 indexes on table T2

IXAM has columns (X1 ASC , Y1 ASC , Z1 ASC)
IXBK has columns (Y1 ASC , X1 ASC, Z1 ASC)



Expected Output :

T_name || Col_name
------- ---------

T1 || A1 ASC , B1 ASC
T1 || B1 ASC , A1 ASC
T1 || C1 ASC
T2 || X1 ASC , Y1 ASC , Z1 ASC
T2 || Y1 ASC , X1 ASC, Z1 ASC

However when i run a simple query the out put i get is :

T_name || Col_name
------- || --------
T1|| A1 ASC
T1 || B1 ASC
T1 || B1 ASC
T1 || A1 ASC
T1 || C1 ASC
T2 || X1 ASC
T2 || Y1 ASC
T2 || Z1 ASC

Desired Output :

T_name || Col_name
------- || --------
T1 || A1 ASC , B1 ASC
T1 || B1 ASC, A1 ASC
T1 || C1 ASC
T2 || X1 ASC , Y1 ASC , Z1 ASC
T2 || Y1 ASC , X1 ASC, Z1 ASC


Using : MIN(sk.column_name || ' '|| case sk.ordering when 'A' then 'ASC' else 'DESC' end) || COALESCE(',' || NULLIF( MAX(sk.column_name || ' '||case sk.ordering when 'A' then 'ASC' else 'DESC' end) , min(sk.column_name ||' '|| case sk.ordering when 'A' then 'ASC' else 'DESC' end) ) , '')

OUTPUT IS :

T_name || Col_name
------- || --------
IXBO || A1 ASC , B1 ASC
IXCO || A1 ASC, B1 ASC
IXDO || C1 ASC

Kindly help with your precious time and guidance please.
Please guide with a sql. thanks in anticipation..

Yes your intention is very clear to me.
The query is excellent works perfect where A,B are limited to 2.
But when an index is based on 3 columns the query gives incorrect output.


SAMPLE DATA :

There are 3 indexes on a table T1 :

IXBO has columns ( A1 ASC , B1 ASC ) ;

IXCO has columns( B1 ASC , A1 ASC ) ;

IXDO ( C1 ASC ) ;

There are 2 indexes on table T2

IXAM has columns (X1 ASC , Y1 ASC , Z1 ASC)
IXBK has columns (Y1 ASC , X1 ASC, Z1 ASC)



Expected Output :

T_name || Col_name
------- ---------

T1 || A1 ASC , B1 ASC
T1 || B1 ASC , A1 ASC
T1 || C1 ASC
T2 || X1 ASC , Y1 ASC , Z1 ASC
T2 || Y1 ASC , X1 ASC, Z1 ASC

However when i run a simple query the out put i get is :

T_name || Col_name
------- || --------
T1|| A1 ASC
T1 || B1 ASC
T1 || B1 ASC
T1 || A1 ASC
T1 || C1 ASC
T2 || X1 ASC
T2 || Y1 ASC
T2 || Z1 ASC

Desired Output :

T_name || Col_name
------- || --------
T1 || A1 ASC , B1 ASC
T1 || B1 ASC, A1 ASC
T1 || C1 ASC
T2 || X1 ASC , Y1 ASC , Z1 ASC
T2 || Y1 ASC , X1 ASC, Z1 ASC


Using : MIN(sk.column_name || ' '|| case sk.ordering when 'A' then 'ASC' else 'DESC' end) || COALESCE(',' || NULLIF( MAX(sk.column_name || ' '||case sk.ordering when 'A' then 'ASC' else 'DESC' end) , min(sk.column_name ||' '|| case sk.ordering when 'A' then 'ASC' else 'DESC' end) ) , '')

OUTPUT IS not right where index is based on 3 columns :

Kindly help with your precious time and guidance please.
Please guide with a sql. thanks in anticipation..

I have put an attachment of the query and the output that i get after executing the query. Please guide.

With DB2 v5, neither recursive SQL nor XML aggregation are supported.
So the short answer to your question is: "this is impossible".

There is, however, a (relatively complex, but working) workaround,
*provided* there is a guaranteed upper bound on the number of rows to be aggregated.

Is that the case? And if so, what is that upper bound?
(The query's complexity will increase with this upper bound.)

DB2 v5 isn't supported at all. I suggest the OP upgrades to a version that isn't 15 years old.

p.s: One could write user-defined aggregate functions with some tricks (combining shared memory with built-in aggregate functions - on non-DPF systems only). But that's not really for the faint of heart.

Sure.
On the other hand, the v8 or 9 solutions (using either recursive SQL or XML) are non-standard SQL. For a reasonable subset of the sketched problem, there exists a standard SQL solution (which coincidentally would also work with DB2 v5).