Professional Web Applications Themes

Concatenate column values from multiple rows - IBM DB2

Greetings, Would it be possible to construct SQL to concatenate column values from multiple rows? SELECT ... FROM T1, T2 WHERE T1.key=T2.fkey (group by key?); The following is an example: Table 1 (key and other columns): key --- A B C Table 2 (fkey, col1 etc.): fkey col1 ---- ---- A 1 A 2 A 3 B 1 B 2 C 4 The SQL to be constructed should return ALL col1 values concatenated for the same key column: key con-col1 --- -------- A 123 B 12 C 4 Any hints or suggestions would be greatly appreciated. Platform is DB2 V8 ...

  1. #1

    Default Concatenate column values from multiple rows

    Greetings,

    Would it be possible to construct SQL to concatenate column values
    from multiple rows?

    SELECT ... FROM T1, T2 WHERE T1.key=T2.fkey (group by key?);


    The following is an example:

    Table 1 (key and other columns):
    key
    ---
    A
    B
    C

    Table 2 (fkey, col1 etc.):
    fkey col1
    ---- ----
    A 1
    A 2
    A 3
    B 1
    B 2
    C 4

    The SQL to be constructed should return ALL col1 values concatenated
    for the same key column:

    key con-col1
    --- --------
    A 123
    B 12
    C 4

    Any hints or suggestions would be greatly appreciated.

    Platform is DB2 V8 on W2K + FP2.
    nek Guest

  2. #2

    Default Re: Concatenate column values from multiple rows

    nek <nekiv90hotmail.com> wrote:
    > Greetings,
    >
    > Would it be possible to construct SQL to concatenate column values
    > from multiple rows?
    >
    > SELECT ... FROM T1, T2 WHERE T1.key=T2.fkey (group by key?);
    >
    >
    > The following is an example:
    >
    > Table 1 (key and other columns):
    > key
    > ---
    > A
    > B
    > C
    >
    > Table 2 (fkey, col1 etc.):
    > fkey col1
    > ---- ----
    > A 1
    > A 2
    > A 3
    > B 1
    > B 2
    > C 4
    >
    > The SQL to be constructed should return ALL col1 values concatenated
    > for the same key column:
    >
    > key con-col1
    > --- --------
    > A 123
    > B 12
    > C 4
    >
    > Any hints or suggestions would be greatly appreciated.
    You could use recursive SQL like this:

    WITH x(key, val, rnum) AS
    ( SELECT fkey, col1, row_number() over(partition by fkey)
    FROM myTable ),
    y(key, str, cnt, cnt_max) AS
    ( SELECT key, VARCHAR('', 1000), 0, MAX(rnum)
    FROM x
    GROUP BY key
    UNION ALL
    SELECT y.key, y.str || RTRIM(CHAR(x.val)), y.cnt + 1, y.cnt_max
    FROM x, y
    WHERE x.key = y.key AND
    x.rnum = y.cnt + 1 AND
    y.cnt < y.cnt_max )
    SELECT key, str
    FROM y
    WHERE y.cnt = y.cnt_max;

    "myTable" corresponds to the second table you listed above. If you want to
    restrict the result in some way, you only have to modify the common table
    expression named "x".

    --
    Knut Stolze
    Information Integration
    IBM Germany / University of Jena
    Knut Stolze Guest

  3. #3

    Default Re: Concatenate column values from multiple rows

    Thanks Knut! It returned what was expected.

    One problem is its reponse time. Here is the result of running it in
    db2batch:

    Stats:
    (1) MyTable: 80,000 rows of 97 bytes record length
    (2) x: 15,000 rows of 85 bytes record length

    Elapsed Time (s) 2914.922
    Rows fetched: 5752
    Rows printed: 5752

    Arith. mean: 2914.922
    Geom. mean: 2914.922

    Not really optimal considering these tables being two of the six
    tables joined in the query, indexed and clustered on 'key' and 'fkey'
    respectively. The query was run after reorg.

    Best Regards,
    Nek.


    Knut Stolze <stolzede.ibm.com> wrote in message news:<bk6lba$rl9$1fsuj29.rz.uni-jena.de>...
    > nek <nekiv90hotmail.com> wrote:
    >
    > > Greetings,
    > >
    > > Would it be possible to construct SQL to concatenate column values
    > > from multiple rows?
    > >
    > > SELECT ... FROM T1, T2 WHERE T1.key=T2.fkey (group by key?);
    > >
    > >
    > > The following is an example:
    > >
    > > Table 1 (key and other columns):
    > > key
    > > ---
    > > A
    > > B
    > > C
    > >
    > > Table 2 (fkey, col1 etc.):
    > > fkey col1
    > > ---- ----
    > > A 1
    > > A 2
    > > A 3
    > > B 1
    > > B 2
    > > C 4
    > >
    > > The SQL to be constructed should return ALL col1 values concatenated
    > > for the same key column:
    > >
    > > key con-col1
    > > --- --------
    > > A 123
    > > B 12
    > > C 4
    > >
    > > Any hints or suggestions would be greatly appreciated.
    >
    > You could use recursive SQL like this:
    >
    > WITH x(key, val, rnum) AS
    > ( SELECT fkey, col1, row_number() over(partition by fkey)
    > FROM myTable ),
    > y(key, str, cnt, cnt_max) AS
    > ( SELECT key, VARCHAR('', 1000), 0, MAX(rnum)
    > FROM x
    > GROUP BY key
    > UNION ALL
    > SELECT y.key, y.str || RTRIM(CHAR(x.val)), y.cnt + 1, y.cnt_max
    > FROM x, y
    > WHERE x.key = y.key AND
    > x.rnum = y.cnt + 1 AND
    > y.cnt < y.cnt_max )
    > SELECT key, str
    > FROM y
    > WHERE y.cnt = y.cnt_max;
    >
    > "myTable" corresponds to the second table you listed above. If you want to
    > restrict the result in some way, you only have to modify the common table
    > expression named "x".
    nek Guest

  4. #4

    Default Re: Concatenate column values from multiple rows

    nek <nekiv90hotmail.com> wrote:
    > One problem is its reponse time. Here is the result of running it in
    > db2batch:
    >
    > Stats:
    > (1) MyTable: 80,000 rows of 97 bytes record length
    > (2) x: 15,000 rows of 85 bytes record length
    >
    > Elapsed Time (s) 2914.922
    > Rows fetched: 5752
    > Rows printed: 5752
    >
    > Arith. mean: 2914.922
    > Geom. mean: 2914.922
    >
    > Not really optimal considering these tables being two of the six
    > tables joined in the query, indexed and clustered on 'key' and 'fkey'
    > respectively. The query was run after reorg.
    Maybe using aggregate functions (where the aggregation is actually the
    concatenation) would be better performing:
    [url]http://www7b.boulder.ibm.com/dmdd/library/techarticle/0309stolze/0309stolze.html[/url]

    --
    Knut Stolze
    Information Integration
    IBM Germany / University of Jena
    Knut Stolze Guest

  5. #5

    Default Re: Concatenate column values from multiple rows

    Thanks Knut.

    Yeah, I tried to look for solution like 'select key, getaggregate(key)
    ...from..'
    where getaggregate(key) returns the concatenation of columns discussed
    before.

    Admittedly it is a table design flaw. The temporary solution is to
    pass all rows returned for the key to reporting tools such as Business
    Objects or Crystal Reports where they can be massaged.

    I'll experiment the aggregate option you suggested, hopefully with
    better response time.

    Best Regards,
    Nek.


    Knut Stolze <ibm.com> wrote in message news:<bkm9fo$trk$rz.uni-jena.de>... 
    >
    > Maybe using aggregate functions (where the aggregation is actually the
    > concatenation) would be better performing:
    > http://www7b.boulder.ibm.com/dmdd/library/techarticle/0309stolze/0309stolze.html[/ref]
    nek Guest

  6. #6

    Default Re: Concatenate column values from multiple rows

    nek <com> wrote:
     

    Some other thought:
    (+) indexes with include columns to avoid table scans
    (+) generated columns
    (+) the usual performance evaluation and improvement techniques, starting
    with ysing the access plan

    --
    Knut Stolze
    Information Integration
    IBM Germany / University of Jena
    Knut Guest

Similar Threads

  1. Concatenate fields with no values
    By Pubcit in forum Coldfusion Database Access
    Replies: 1
    Last Post: July 15th, 03:41 PM
  2. Can you concatenate recordset values in one field?
    By tim-p in forum Dreamweaver AppDev
    Replies: 5
    Last Post: April 21st, 09:06 PM
  3. multiple menu/list values inserted into one mysql column
    By Nimotek in forum Dreamweaver AppDev
    Replies: 1
    Last Post: February 16th, 04:23 PM
  4. Replies: 0
    Last Post: October 4th, 04:02 PM
  5. How to concatenate rows of data into one field?
    By Larry Trutter in forum IBM DB2
    Replies: 1
    Last Post: September 4th, 09:23 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139