Archives of the TeradataForum
Message Posted: Mon, 12 Jan 2004 @ 20:54:35 GMT
| Subj: | | Re: How to concatenate the column values |
| |
| From: | | Victor Sokovin |
Vivek,
| | I have a table with the following rows. | |
> C1 C2
>=========
> 1 AB
> 1 BC
> 1 CA
| | I have to concatenate all the values of C2 in to a single field for a given C1 value like this. | |
> C1 C2
> ===============
> 1 AB BC CA
| | Do u have an idea how to do this. ? I am not supposed to use a shell script for this. | |
This sounds like a new aggregate function, something like SUM for strings. There is surely nothing like this in TD as yet. If you really
need the most generic form of it, I think cursors would be the main option.
To stay in SQL, you need to simplify the task. For example, you might add a condition that C1 cannot have more than n rows with identical
values, for some fixed n. Then you could try the n-fold self-join of the table on C1 (n cannot be > 64 in TD but should be even smaller as the
number of combinations grows as n to the n-th). That set would contain all possible combinations of C2 values and they are quite easy to
concatenate. Of course, you will still need to chose the right combination for each C1 value. You don't really specify how you define it. Perhaps
you can already define more predicates when you self-join the table and thus reduce the number of combinations?
That's all I can think of right now. If something else comes up I'll report back.
Regards,
Victor
| 
