Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Mon, 12 Jan 2004 @ 20:54:35 GMT

  <Prev Next>   <<First <Prev Next> Last>>  

Subj:   Re: How to concatenate the column values
From:   Victor Sokovin


  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.



  <Prev Next>   <<First <Prev Next> Last>>  
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023