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]

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

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();

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:

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.

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

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

Powerful!

[srikanth2254]

CREATE TABLE ABC
(
ID int,
roadnum varchar(50)
)



INSERT INTO ABC VALUES (1,'A-1');
INSERT INTO ABC VALUES (2,'B-1');
INSERT INTO ABC VALUES (2,'E-1');
INSERT INTO ABC VALUES (3,'N-1');
INSERT INTO ABC VALUES (4,'N-3');
INSERT INTO ABC VALUES (4,'E-2');
INSERT INTO ABC VALUES (4,'CA-1');
INSERT INTO ABC VALUES (5,'E-1');

SELECT * FROM ABC;


SELECT DISTINCT ID,
ARRAY_TO_STRING(ARRAY(SELECT ROADNUM FROM ABC WHERE ID=T1.ID),'/') AS
ROADNUM
FROM ABC T1;