5

I have a list of merchants that do business in different states.

Merch    State

A          NC

A          FL

B          CA

B          VA

Instead of returning four records I want to group by Merch but concatenate the strings of the states so that the output looks like

Merch      States

A           NC,FL

B           CA,VA

I'm having a lot of trouble translating the response in this answer for my issue Optimal way to concatenate/aggregate strings

I also cannot get String_agg to work, I'm not sure it works in Teradata. https://learn.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql

JNevill
  • 46,980
  • 4
  • 38
  • 63
JF4bes
  • 147
  • 1
  • 4
  • 12

1 Answers1

7

You can use the xml_agg() built in function in Teradata. This doesn't translate well from SQL Server which is why you are having issues with those two links. 

SELECT merch, TRIM(TRAILING ',' FROM (XMLAGG(States || ',' ORDER BY States) (VARCHAR(500))))
FROM yourtable
GROUP BY 1;
JNevill
  • 46,980
  • 4
  • 38
  • 63
  • Thank you so much for the answer. It seems to be a great start. I pulled the top 200 and in the Teradata Answerset it shows "XMLAGG((Merch_state001.xml and so forth. These were saved to files on my computer, which are the correct string if I open them in Notepad. I don't really understand what these XML files are but can extract the text from them? – JF4bes Jul 25 '17 at 14:52
  • 1
    It's not so much that it's XML. We are just borrowing the `xml_agg` function here to generate a comma delimited list which is just a string. If it's giving you oddball outputs you could toss a CAST() around that xmlagg like `CAST(XMLAGG(States || ',' ORDER BY States) AS VARCHAR(500))` so you are definitely dealing with a varchar field type in your output. – JNevill Jul 25 '17 at 15:04
  • The syntax for casting XMLAGG is a little funky, to me at least. This works for me: `TRIM(TRAILING ',' FROM (XMLAGG(state || ',' ORDER BY state ) (VARCHAR(10000))))`. – Andrew Jul 25 '17 at 15:12
  • 'select top 20 merch, translate(xmlagg(merch_state || ',' ORDER BY merch_state) as VARCHAR(500)) from table1 group by 1; ' Is throwing this error for me http://www.info.teradata.com/HTMLPubs/DB_TTU_14_10/index.html#page/General_Reference/B035_1096_112K/Database.27.1563.html any ideas? Thanks so much, Sorry I have no idea how to format this. – JF4bes Jul 25 '17 at 15:16
  • I've updated the answer with the suggestion from @Andrew I believe that should do the trick – JNevill Jul 25 '17 at 18:34