Hi all, I'm trying to write a function that takes the following records
| Field1 | Field2 | Field3 | Field 4 | A | P | Name1 | 51 | A | P | Name2 | 20.143 | A | P | Name3 | 32.7 | A | P | Name4 | 5.22 | A | P | Name5 | 14.34 | A | Q | Name2 | 1.111 | A | Q | Name7 | 9.712 | A | Q | Name3 | 2.33 | A | Q | Name1 | 77 | B | P | Name1 | 75 | B | P | Name4 | 2.66 | B | P | Name5 | 2.63
And turn it into:
| Field1 | Field 2 | 1st | 2nd | 3rd | 4th | 5th |
| A | P | Name1: 51 | Name3: 32.7 | Name2: 20.143 | Name5: 14.34 | Name4: 5.22 |
| A | Q | Name1: 77 | Name7: 9.712 | Name3: 2.33 | Name2: 1.111 | Null |
| B | P | Name1: 75 | Name4: 2.66 | Name5: 2.63 | Null | Null |
etc.
Sort of like a crosstab query, but populating the columns in order of the value in Field 4 and then concatenating with Field3
My approach to writing this function would be as below, but I can't figure out how to do the bread and butter of the function: assign the values into fields according to their order. Any help or ideas would be greatly appreciated.
Many thanks,
Eric
CREATE FUNCTION sorta-crosstab RETURNS setof RECORD AS
DECLARE
crFields CURSOR FOR SELECT Field1,Field2,Field3,Field4 FROM table1 ORDER BY field1 ASC, field2 ASC, field4 DESC;
rwFields table1%ROWTYPE;
rcResults RECORD;
BEGIN
OPEN crFields
LOOP -- over rows
FETCH crFields INTO rwFields;
EXIT WHEN NOT FOUND
IF ... THEN - Loop over equal values of Field1 and Field2
-- Add to rcResults field for "1st","2nd", "3rd", etc. \ These are what I don't know how to do!
-- Assign value to field "1st","2nd","3rd",etc. /
ELSE
RETURN NEXT;
END;
END LOOP;
RETURN;
END;
---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your message can get through to the mailing list cleanly
