By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,988 Members | 1,360 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,988 IT Pros & Developers. It's quick & easy.

Aggregate/concatenate function (flattening columns into a row)

P: 6
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
Jun 12 '07 #1
Share this Question
Share on Google+
4 Replies


shoonya
100+
P: 161
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
Jun 14 '07 #2

Expert 100+
P: 534
This should work for you

Expand|Select|Wrap|Line Numbers
  1. CREATE or replace FUNCTION flatten() RETURNS integer AS $$
  2. DECLARE
  3.     rec RECORD ;
  4.     cnt integer ;
  5.     delim varchar := '/';
  6. BEGIN
  7.      FOR rec IN SELECT * FROM tab1 ORDER BY ID LOOP
  8.         select count(1) into cnt from tab2 where tab2.ID = rec.ID;
  9.         if cnt = 0 then
  10.            insert into tab2 values (rec.ID, rec.roadnum);
  11.         else
  12.            update tab2 set roadnum = roadnum || delim || rec.roadnum 
  13.                where tab2.ID = rec.ID;
  14.         end if;
  15.     END LOOP;
  16.  
  17.     RETURN (select count (*) from tab2);
  18. END;
  19. $$ LANGUAGE plpgsql;
  20.  
here's the result of running select flatten();
Expand|Select|Wrap|Line Numbers
  1. postgres=# select * from tab1;
  2.   id   | roadnum
  3. -------+-----
  4.      1 | A1
  5.      2 | B1
  6.      2 | B2
  7.      3 | C1
  8.      3 | C2
  9.      3 | C3
  10.      4 | D1
  11.      4 | D2
  12.      4 | D3
  13.      4 | D4
  14.      5 | E1
  15.      6 | F1
  16.      7 | G1
  17.      7 | G2
  18.      8 | J1
  19.      8 | J2
  20.      9 | H1
  21.     10 | K1
  22. (18 rows)
  23.  
  24.  
  25. postgres=# select * from tab2;
  26.  id    | roadnum
  27. -------+-------------
  28.      1 | A1
  29.      2 | B1/B2
  30.      3 | C1/C2/C3
  31.      4 | D1/D2/D3/D4
  32.      5 | E1
  33.      6 | F1
  34.      7 | G1/G2
  35.      8 | J1/J2
  36.      9 | H1
  37.     10 | K1
  38. (10 rows)
  39.  
Jun 14 '07 #3

P: 1
You may find this useful:
Expand|Select|Wrap|Line Numbers
  1. SELECT array_to_string(array(SELECT column_name FROM table_name), ':');
  2.  
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
  1. SELECT array_to_string(array(SELECT roadnum FROM table_name WHERE ID=4), ':');
  2.  
would return "N-3:E-2:CA-1" .

Powerful!
Aug 8 '08 #4

P: 1
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;
Aug 13 '10 #5

Post your reply

Sign in to post your reply or Sign up for a free account.