Using PostgreSQL 8.4, I have successfully been able to use array_agg() to take a case of multiple orders and make a single row per customers:
From this:
order_id|customer_id|order_date |order_desc
1 |1 |"2010-01-01"|"Tom's First"
2 |1 |"2010-04-01"|"Tom's Second"
7 |1 |"2010-04-13"|"Tom's Third"
8 |1 |"2011-04-13"|"Tom's Last"
5 |1 |"2011-06-20"|"Tom's Really Last."
3 |2 |"2010-07-07"|"Dick's First"
6 |2 |"2011-07-07"|"Dick's Other"
4 |3 |"2011-04-04"|"Harry's Only"
Using this:
select cu.customer, array_agg(ord.order_id) as orders from test_order ord
inner join test_customer cu
on ord.customer_id = cu.customer_id
group by cu.customer
results in:
customer |orders
"Tom" |"{1,2,7,8,5}"
"Dick" |"{3,6}"
"Harry" |"{4}"
And I can grab pieces of the array to create new columns if I hard code each iteration:
select cu.customer,
(array_agg(ord.order_id))[1] as order_1,
(array_agg(ord.order_id))[2] as order_2,
(array_agg(ord.order_id))[3] as order_3,
(array_agg(ord.order_id))[4] as order_4,
(array_agg(ord.order_id))[5] as order_5
from test_order ord
inner join test_customer cu
on ord.customer_id = cu.customer_id
group by cu.customer
results in:
customer|order_1|order_2|order_3|order_4|order_5
"Dick" |3 |6 | | |
"Harry" |4 | | | |
"Tom" |8 |1 |5 |2 |7
However, what I'd like to do, in two steps:
For Loop my way through the records so that I don't have to create every iteration of the field. The good news is that the structure above doesn't error and just passes NULL, but if I ever get to some insane number of records, I don't have to have to keep creating order_55, order_56 etc manually in my statement.
Even better would be eventually to not pass it a specific field and have it iterate through all the fields (barring the customer_id) and give me iterations of each field, to the effect of:
customer|order_id1|order_date1|order_desc1|order_id2|order_date2|order_desc2| ...etc etc. Basically joining the parent table (customer) to the child (order), but having multiple child records go across a single row instead of creating multiples.
(Yes, I understand that this goes against the basic concept of why you do parent/child tables in the first place. However, I pass this on to a client and this would make the process infinitely easier.)
UPDATE: I've gotten closer with the cannibalized function ... the first time I call it, it creates the columns and populates one of the customers. But for some reason, my IF NOT EXISTS works when ran separately, but not within the function: I get a "column order_id1 exists" error. I'd also like to eventually modify this so the specific fields aren't hard coded; instead of the customer_id I'd like to do something like pass the parent table, child table and joining ID, and have it fully append the child table in this crosstab-ish manner.
CREATE FUNCTION loop_test(integer) RETURNS integer AS $$
DECLARE
rOrder RECORD;
loop_counter INT := 1;
target_customer_id ALIAS FOR $1;
BEGIN
FOR rOrder IN SELECT *
FROM vdad_data.test_order
WHERE customer_id = target_customer_id
ORDER BY order_id LOOP
IF NOT EXISTS
(
SELECT * FROM information_schema.COLUMNS
WHERE COLUMN_NAME= 'order_id' || loop_counter
AND TABLE_NAME='test_customer'
AND TABLE_SCHEMA='vdad_data'
)
THEN
EXECUTE 'ALTER TABLE vdad_data.test_customer
ADD COLUMN order_id' || loop_counter || ' integer';
END IF;
IF NOT EXISTS
(
SELECT * FROM information_schema.COLUMNS
WHERE COLUMN_NAME= 'order_date' || loop_counter
AND TABLE_NAME='test_customer'
AND TABLE_SCHEMA='vdad_data'
)
THEN
EXECUTE 'ALTER TABLE vdad_data.test_customer
ADD COLUMN order_date' || loop_counter || ' date';
END IF;
IF NOT EXISTS
(
SELECT * FROM information_schema.COLUMNS
WHERE COLUMN_NAME= 'order_desc' || loop_counter
AND TABLE_NAME='test_customer'
AND TABLE_SCHEMA='vdad_data'
)
THEN
EXECUTE 'ALTER TABLE vdad_data.test_customer
ADD COLUMN order_desc' || loop_counter || ' character varying';
END IF;
EXECUTE 'UPDATE vdad_data.test_customer
SET order_id' || loop_counter || ' = ' || rOrder.order_id ||',
order_date' || loop_counter || ' = ' || quote_literal(to_char(rOrder.order_date,'yyyy-mm-dd')) ||',
order_desc' || loop_counter || ' = ' || quote_literal(rOrder.order_desc) ||'
WHERE customer_id = ' ||rOrder.customer_id;
loop_counter = loop_counter + 1;
END LOOP;
RETURN 1;
END;
$$ LANGUAGE plpgsql;
I apologize for being all over the map, as I've been trying to tackle several things about this at once that I can't quite get. Any help is appreciated, thanks!
