Netezza- Concatenate Different Values from Single Column based on Order from another Column

Question

I am trying to do a column concatenate based on order of Sample Counter and by ID field. Below is an example of the data

**Heat ID**  **Sample Type**  **Sample Counter**
  466170            T1                2 
  466170            L0                3
  466170            C1                4
  466170            V2                1
  580910            C1                1
  580910            L0                2
  580910            T1                3  

This is what I want below. So I want it concatenated by ascending sample counter I guess you could say.

**Heat ID**  **Concat Code**
  466170       V2_T1_L0_C1
  580910       C1_L0_T1

The data is structured so that not every heat ID will have the same amount of Sample Types and the Sample Types are in different order. Sample counter is when the different Sample Type is used (The Order). Any help would be greatly appreciated. Thank you!

Answer 1

There are several ways to approach this. String aggregation is generally rather database specific. However, the counter column allows conditional aggregation instead:

select heatid,
       (max(case when samplecounter = 1 then sample_type else '' end) ||
        max(case when samplecounter = 2 then '_' || sample_type  else '' end) ||
        max(case when samplecounter = 3 then '_' || sample_type  else '' end) ||
        max(case when samplecounter = 4 then '_' || sample_type  else '' end) ||
        max(case when samplecounter = 5 then '_' || sample_type  else '' end)
       ) as concat_code

from t
group by heatid;

Note you need enough conditional statements for the maximum sample counter.

Also, some databases spell || as + or even require the explicit use of a concat() function.