Column Concatenation Function?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

kaming - 15 Dec 2005 10:15 GMT

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

Reply to this Message

Knut Stolze - 15 Dec 2005 12:08 GMT

> Dear all,
>
[quoted text clipped - 22 lines]
> 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.

Signature

Knut Stolze
DB2 Information Integration Development
IBM Germany

Reply to this Message

--CELKO-- - 16 Dec 2005 13:19 GMT

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.

Reply to this Message

Buck Nuggets - 16 Dec 2005 14:48 GMT

> 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

Reply to this Message

Bob Stearns - 16 Dec 2005 17:05 GMT

> Why do you wish to destroy first normal form (1NF)?
>
[quoted text clipped - 10 lines]
> 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.

Reply to this Message

--CELKO-- - 17 Dec 2005 19:04 GMT

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.

Reply to this Message