special concatenate of data

Question

I'm trying to concatenate some creditcards data I have on my customers.

For example I'm having the next table (call it Table A):

custNum  totAmount  creditCardType
  10        100        Visa
  10        250        Mastercard
  27        350        Visa
  10        500        AmericanExpress 
  27        100        AmericanExpress

etc...

and I'm trying to concatenate this data in the next format:

    custNum   totAmount    creditCardType
      10        850        Visa,Mastercard,AmericanExpress 
      27        450        Visa,AmericanExpress

is there a smart and simple way of doing it?

Thank you in advance!

Voting for Reopen: This type of functonality requires DBMS-specific answers as there's no Standard SQL was, None of the linked answers contains an answer working efficiently on a `Teradata` system. — dnoeth, Aug 21 '16 at 09:36

score 2 · Accepted Answer · edited May 23 '17 at 12:08

In Teradata there are two possible solutions:

#1: a Recursive Query, always returns an ordered list. Might use lots of spool when there are many rows to concat (not in your case when using credit card types). Additonally you need to create a Volatie Table to materialize a ROW_NUMBER: see this accepted answer

#2: XMLAGG, if XML-Services are installed on your system, much simpler syntax, retunrs an ordered list optionally: see this accepted answer

special concatenate of data

1 Answers1