Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Apr 2011
    Posts
    31

    Red face Unanswered: Delimit column value by ','

    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

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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).

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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) ) , '')
    Last edited by tonkuma; 05-12-11 at 11:08. Reason: Corrected my poor English description to "number of duplicated rows for each value of (A, B)"

  4. #4
    Join Date
    Apr 2011
    Posts
    31

    delitmi values with ','

    Quote Originally Posted by tonkuma View Post
    If the max number of duplication 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.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    The col_name for IXCO should be B1 ASC, A1 ASC.
    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.
    Last edited by tonkuma; 05-12-11 at 09:11. Reason: Add description of DB2 catalog.

  6. #6
    Join Date
    Apr 2011
    Posts
    31

    Red face delimit column with ','

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

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.
    http://www.dbforums.com/db2/1666067-...e-session.html

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

  8. #8
    Join Date
    Apr 2011
    Posts
    31

    Red face delimit column with ','

    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.

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.
    http://www.dbforums.com/db2/1666236-help-sql.html
    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.
    Last edited by tonkuma; 05-12-11 at 22:01. Reason: A little modified and added some phrase to last sentence.

  10. #10
    Join Date
    Apr 2011
    Posts
    31

    Red face delimit column with ','

    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..

  11. #11
    Join Date
    Apr 2011
    Posts
    31

    Red face delimit column with ','

    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..

  12. #12
    Join Date
    Apr 2011
    Posts
    31

    Red face delimit column with ','

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

  13. #13
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    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.)
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  14. #14
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by Peter.Vanroose View Post
    With DB2 v5, neither recursive SQL nor XML aggregation are supported.
    So the short answer to your question is: "this is impossible".
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  15. #15
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by stolze View Post
    DB2 v5 isn't supported at all.
    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).
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •