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;