Aggregate/concatenate function (flattening columns into a row)

mattdoughty

Hi Forum,

I'm trying to perform a type of concatenate function on a table and am not really sure where to start. I'd like to aggregate the values from 'field' on their ID. To explain, my table is like this:

ID roadnum
1 A-1
2 B-1
2 E-1
3 N-1
4 N-3
4 E-2
4 CA-1
5 E-1

Which I'd like to transform to:

ID roadnum
1 A-1
2 B-1 / E-1
3 N-1
4 E-2 / CA-1 / N-3
5 E-1

I've been looking at various lists and forums but I've not been able to follow the explanations. Further down the line, instead of simply concatenating the field values, I'd like to concatenate according to the road importance- which is based on the roadnum, but one step at a time. When I have the values in the same field, I'll worry about the next step.

Matt

shoonya

Quote:

Originally Posted by mattdoughty

Hi Forum,

I'm trying to perform a type of concatenate function on a table and am not really sure where to start. I'd like to aggregate the values from 'field' on their ID. To explain, my table is like this:

ID roadnum
1 A-1
2 B-1
2 E-1
3 N-1
4 N-3
4 E-2
4 CA-1
5 E-1

Which I'd like to transform to:

ID roadnum
1 A-1
2 B-1 / E-1
3 N-1
4 E-2 / CA-1 / N-3
5 E-1

I've been looking at various lists and forums but I've not been able to follow the explanations. Further down the line, instead of simply concatenating the field values, I'd like to concatenate according to the road importance- which is based on the roadnum, but one step at a time. When I have the values in the same field, I'll worry about the next step.

Matt

you can use the array data tye
make the roadnum attribute an array and append the routes in the last
check array data type from postgreSQL documentation

but be careful as when you will select the array from databse using php or ny other language it will return only a string {value1,value2...}
so iterate nd make it an array

shoonya

michaelb

This should work for you

Expand|Select|Wrap|Line Numbers

 
CREATE or replace FUNCTION flatten() RETURNS integer AS $$

DECLARE

    rec RECORD ;

    cnt integer ;

    delim varchar := '/';

BEGIN

     FOR rec IN SELECT * FROM tab1 ORDER BY ID LOOP

        select count(1) into cnt from tab2 where tab2.ID = rec.ID;

        if cnt = 0 then

           insert into tab2 values (rec.ID, rec.roadnum);

        else

           update tab2 set roadnum = roadnum || delim || rec.roadnum 

               where tab2.ID = rec.ID;

        end if;

    END LOOP;
 
    RETURN (select count (*) from tab2);

END;

$$ LANGUAGE plpgsql;

here's the result of running select flatten();

Expand|Select|Wrap|Line Numbers

 
postgres=# select * from tab1;

  id   | roadnum

-------+-----

     1 | A1

     2 | B1

     2 | B2

     3 | C1

     3 | C2

     3 | C3

     4 | D1

     4 | D2

     4 | D3

     4 | D4

     5 | E1

     6 | F1

     7 | G1

     7 | G2

     8 | J1

     8 | J2

     9 | H1

    10 | K1

(18 rows)
 
postgres=# select * from tab2;

 id    | roadnum

-------+-------------

     1 | A1

     2 | B1/B2

     3 | C1/C2/C3

     4 | D1/D2/D3/D4

     5 | E1

     6 | F1

     7 | G1/G2

     8 | J1/J2

     9 | H1

    10 | K1

(10 rows)

Liam

You may find this useful:

Expand|Select|Wrap|Line Numbers

SELECT array_to_string(array(SELECT column_name FROM table_name), ':');

Change the inner SELECT to any query that returns only one column.
This will concatenate all values into one string, with a ':' between each value.

Expand|Select|Wrap|Line Numbers

 
SELECT array_to_string(array(SELECT roadnum FROM table_name WHERE ID=4), ':');

would return "N-3:E-2:CA-1" .

Powerful!

Aggregate/concatenate function (flattening columns into a row)

re: Aggregate/concatenate function (flattening columns into a row)

re: Aggregate/concatenate function (flattening columns into a row)

re: Aggregate/concatenate function (flattening columns into a row)

Similar PostgreSQL Database bytes

/bytes/development

/bytes/it

/bytes/about