Column Concatenation Function?

[kaming]

Dear all,

I would like to ask is these any DB2 function that can concatenation strings
and have grouping capability??

for example,

Tables:T

F1 F2
---- ----
A X
A Y
A Z
B M
B N
B O

SQL: select F1, STRCAT(F2,',') from T group by F1
* STRCAT is a imaginary function ..

Result:
1 2
--- ---------
A X,Y,Z
B M,N,O

Thanks in advance!

Henry

[Knut Stolze]

kaming wrote:

Dear all,

I would like to ask is these any DB2 function that can concatenation
strings and have grouping capability??

for example,

Tables:T

F1 F2
---- ----
A X
A Y
A Z
B M
B N
B O

SQL: select F1, STRCAT(F2,',') from T group by F1
* STRCAT is a imaginary function ..

Result:
1 2
--- ---------
A X,Y,Z
B M,N,O

Have a look at recursive queries. Examples for your question are posted
here about twice a month at least.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[--CELKO--]

Why do you wish to destroy first normal form (1NF)?

All values in an RDBMS are scalar. What have you disocovered that Dr.
Codd et al missed for the last 30+ years?

Or are you merely violating the basic principle of a tiered
architecture?

The basic principle of a tiered architecture is that display is done in
the front end and never in the back end. This a more basic programming
principle than just SQL and RDBMS.
Oh, if you still want to be bad programmer, use a cursor so you can
control the ordering. There are a lot of kludges like this posted.

[Buck Nuggets]

--CELKO-- wrote:

<crazy insults snipped>

The basic principle of a tiered architecture is that display is done in
the front end and never in the back end. This a more basic programming
principle than just SQL and RDBMS.

<another useless insult snipped>

Celko, the OP wasn't talking about modeling, he was talking about
querying. Are you saying that this kind of operation should only be
performed in PHP, Java, VB, Python, Ruby, etc? And never in SQL?

Nevermind the performance benefits of doing it in SQL.

Nevermind the opportunity to create a materialized view that could
speed up the operation.

Buck

[Bob Stearns]

--CELKO-- wrote:

Why do you wish to destroy first normal form (1NF)?

All values in an RDBMS are scalar. What have you disocovered that Dr.
Codd et al missed for the last 30+ years?

Or are you merely violating the basic principle of a tiered
architecture?

The basic principle of a tiered architecture is that display is done in
the front end and never in the back end. This a more basic programming
principle than just SQL and RDBMS.
Oh, if you still want to be bad programmer, use a cursor so you can
control the ordering. There are a lot of kludges like this posted.

With this type of thinking, why are there any aggregate functions:
count, sum, avg, etc.? Shouldn't they also be achieved in the front end?
An (or several) aggregate function(s) that worked with strings as
opposed to just numbers, would be greatly appreciated. The whole world
does not consist of accountants and statisticians, SQL xxxx standards
not withstanding. In a perfect world, there would be a way to
declare/define user aggregate functions as well as table and scalar
functions, but a new concept, static, might need to be introduced.

[--CELKO--]

No. An aggregate functions return a *scalar value* of a group
characteristic. This concatenation returns a list *structure*. That
list can be in any order unless you use a cursor and procedural code to
sort it. An aggrgate function that returns an array would also be
wrong becuase it woudl violate 1NF and the results would not be a
table.

there would be a way to declare/define user aggregate functions as well as table and scalar functions, but a new concept, static, might need to be introduced. <<

You would be surprised with what you can do using nested function calls
and expressions right now.

People that argue it is cheaper to do this kind of reporting in the
database forget about the cost of searching, string handling and
disassembly in the front end. The real trick is to have a reporting
tier that holds the data and does the dirt work. The transfer of data
from the RDBMS to the middle tier can be reduced and compressed so that
you do not waste time.