All Forums Database
jhilliar 7 posts Joined 07/14
09 Jul 2014
Mapping Values from many to one relationship to a one to one relationship

Hi Guys,
 
I am brand new to Teradata and writing advanced queries (with data munipulation).  I just got out of school.
I have a column with a corporate code and a column with a descprition to that corporate code.  The problem is each corporate code (which is a number) has many descriptions which all mean bassically the same thing.  It would be really helpful if I could have a column that maps a the corporate code to a concatination of all the descriptions.  I think I may need to use a recursive SQL statement?  I'm not sure.  The other problem is that all of the descriptions have about 10 to 30 spaces after the value, which I will need to get rid of.  Normally I would solve this problem by turning the data to a csv and writing some python.  That said, being in the teradata environment that is not feasible.
 

Currrently:

Corp_ID                      ID_Description
1                                   Apple                          (each have spaces after them)
1                                   Fuji                           (each have spaces after them)
1                                   Grannysmith                       (each have spaces after them)
2                                   Banana                             (each have spaces after them)
2                                   Plantain                         (each have spaces after them)
2                                   Burro                                                (each have spaces after them)
...                                  ...

Ideally:

Corp_ID                      ID_Description
1                                   Apple, Fuji, Grannysmith   (with no auxiliary sapces)
2                                   Banana, Plantain, Burro    (with no auxiliary sapces)
...                                  ...

 
I looked at this post (https://forums.teradata.com/forum/database/ concatenate-value-of-multiple-rows-into-one-single- row-1), but I couldnt really make heads or tails of it.  Any help would be greatly appreciated.
 
Thanks,
Justin

dnoeth 4628 posts Joined 11/04
10 Jul 2014

Hi Justin,
it's easier to answer when you provide additional info:
what's your TD release?
How big is your table?
Is there a known maximum number of desriptions per corp?
Do you need a specific order of descriptions with the result string?
 
Regarding the trailing spaces: TRIM(ID_Description)
 

Dieter

jhilliar 7 posts Joined 07/14
11 Jul 2014

Thank you for the Quick response, I actually just wrote a script in python that generated SQL according to my specidications thanks though.  The script will be hosted at a Web app soon for other peopler with the same problem.  I will update the post when I host it as a link.

You must sign in to leave a comment.