AlmostThere AlmostThere - 1 year ago 241
SQL Question

Combine(concatenate) rows based on dates via SQL

I have the following table.

Animal Vaccine_Date Vaccine
Dog 1/1/2016 x
Dog 2/1/2016 y
Dog 2/1/2016 z
Cat 2/1/2016 y
Cat 2/1/2016 z

I want to be able to combine vaccines that are on the same animal and same date, so that they appear in the same cell. The table below is what the desired end result would be.

Animal Vaccine_Date Vaccine
Dog 1/1/2016 x
Dog 2/1/2016 y,z
Cat 2/1/2016 y,z

I have tried to create a volatile table to do so but I am not having any luck and I don't think Teradata recognizes the Group_concat.

Answer Source

Teradata doesn't have group_concat/listagg functionality. There are a couple of workarounds. My favorite is to use a recursive CTE. It's not terribly efficient, but it's well documented and supported functionality.

In your case:

WITH RECURSIVE recCTE AS ( SELECT Animal, Vaccine_Date, CAST(min(Vaccine) as VARCHAR(50)) as vaccine_list, --big enough to hold concatenated list 1 as depth, --used to determine the largest/last group_concate (the full group) in the final SELECT Vaccine

    FROM table
    GROUP BY 1,2


        recCTE.Vaccine || ',' || table.Vaccine
        recCTE.depth + ,
    FROM recCTE 
        INNER JOIN table ON
            recCTE.Animal = table.Animal AND
            recCTE.Vaccine_Date =  Table.Vaccine_Date
            table.vaccine > recCTE.vaccine

--Now select the result with the largest depth for each animal/vaccine_date combo
QUALIFY ROW_NUMBER() OVER (PARTITION BY animal,vaccine_date ORDER BY depth desc) = 1

You may have to tweak that a little bit (possibly trim the vaccine values before concatenating and whatnot), but it should get you in the ballpark. You can check out the recursive CTE documentation at this link, but it's pretty dry. There are a lot of tutorials out there too, if you are unfamiliar. Teradata's implementation of recursive CTE is very similar to T-SQL and PostgresSQL's implementation as well.

As another option you can check out the as-of-yet undocumented tdstats.udfconcat() as explained by the extremely knowledgable @dnoeth in this thread on Teradata Community website.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download