Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2011
    Posts
    2

    Unanswered: How to display comma seperated in one column

    Table1

    Table1Id Table1Name Table1Phone
    501 AAAAA 1111111111
    502 BBBBB 2222222222
    503 CCCCC 3333333333
    ----------------------------------
    Table2

    Table2Id Table2Name
    1 abc
    2 def
    3 ghi

    ----------------------------------
    Table3

    Table1Id Table2Id
    501 1
    501 2
    501 3
    502 1
    502 2
    503 1
    503 3
    ----------------------------------
    output as below

    Table1Name Table2Name Table1Phone
    AAAAA abc, def, ghi 1111111111
    BBBBB abc, def 2222222222
    CCCCC abc, ghi 3333333333

    I want to display with comma seperte in front end(gridview), is possible with a query and bind to gridview or get the values and do some coding in c# and i am using DB2

    could anyone help in this regard

    Thanks in advance!!!!!!!
    .

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    You gave too little information.
    See http://www.dbforums.com/db2/854783-m...e-posting.html
    ...
    Please follow these guidelines to get quick, apt and meaningful responses :

    1) Every question posted must include your DB2 Version, fixpack and Edition + your Operating System(including version info) + info on any third party software you use.
    You can get his info using the following commands
    db2level -> to get db2 version and fixpack level
    db2licm -l -> to get the db2 type (WSE, ESE, etc)

    ...
    And, it is better(I hope strongly to save my time and to make my work easier)
    to supply CREATE TABLE statement and INSERT statement for test data
    or "WITH common-table-expression" for test data like I used.


    Anyhoiw, this may be an answer
    Example 1:
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     Table1(Table1Id , Table1Name , Table1Phone) AS (
    VALUES
      ( 501 , 'AAAAA' , '1111111111' )
    , ( 502 , 'BBBBB' , '2222222222' )
    , ( 503 , 'CCCCC' , '3333333333' )
    )
    , Table2(Table2Id , Table2Name) AS (
    VALUES
      ( 1 , 'abc' )
    , ( 2 , 'def' )
    , ( 3 , 'ghi' )
    )
    , Table3(Table1Id , Table2Id) AS (
    VALUES
      ( 501 , 1 )
    , ( 501 , 2 )
    , ( 501 , 3 )
    , ( 502 , 1 )
    , ( 502 , 2 )
    , ( 503 , 1 )
    , ( 503 , 3 )
    )
    SELECT Table1Name
         , VARCHAR(
              LISTAGG(Table2Name , ', ')
            , 20
           ) AS Table2Name
         , MAX(Table1Phone) AS Table1Phone
     FROM  Table3 t3
     INNER JOIN
           Table1 t1
       ON  t1.Table1Id = t3.Table1Id
     INNER JOIN
           Table2 t2
       ON  t2.Table2Id = t3.Table2Id
     GROUP BY
           Table1Name
    ;
    ------------------------------------------------------------------------------
    
    TABLE1NAME TABLE2NAME           TABLE1PHONE
    ---------- -------------------- -----------
    AAAAA      abc, ghi, def        1111111111 
    BBBBB      abc, def             2222222222 
    CCCCC      abc, ghi             3333333333 
    
      3 record(s) selected.

  3. #3
    Join Date
    Oct 2011
    Posts
    2
    I am new to DB2 and I tried with the same query getting error as:
    No authorized routine named "Listagg "of type" " having compatible argument was found.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I want to repeat again.

    You gave too little information.
    See http://www.dbforums.com/db2/854783-m...e-posting.html
    ...

    Please follow these guidelines to get quick, apt and meaningful responses :

    1) Every question posted must include your DB2 Version, fixpack and Edition + your Operating System(including version info) + info on any third party software you use.
    You can get his info using the following commands
    db2level -> to get db2 version and fixpack level
    db2licm -l -> to get the db2 type (WSE, ESE, etc)

    ...

    No authorized routine named "Listagg "of type" " having compatible argument was found.
    This information also not sufficient.
    Did you copy and paste the error message?
    See http://www.dbforums.com/db2/854783-m...e-posting.html
    2) If you question is regaring an error, make sure you post the error code, corresponding message and the command/sql which caused the error.
    Last edited by tonkuma; 11-01-11 at 11:05.

Posting Permissions

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