Drew Drew - 7 months ago 67
SQL Question

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

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

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.