Mapping Values from many to one relationship to a one to one relationship

Database
Enthusiast

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

2 REPLIES
Junior Contributor

Re: Mapping Values from many to one relationship to a one to one relationship

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)

Enthusiast

Re: Mapping Values from many to one relationship to a one to one relationship

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.