PostgreSQL - tabla pivote ejemplo 3

CREATE TEMP TABLE t4 (

  timeof   timestamp

 ,entity    character

 ,status    integer

 ,ct        integer);

 INSERT INTO t4 VALUES

  ('2012-01-01', 'a', 1, 1)

 ,('2012-01-01', 'a', 0, 2)

 ,('2012-01-02', 'b', 1, 3)

 ,('2012-01-02', 'c', 0, 4);

SELECT * FROM crosstab(

     'SELECT timeof, entity, status, ct

      FROM   t4

      ORDER  BY 1,2,3'

     ,$$VALUES (1::text), (0::text)$$)

 AS ct ("Section" timestamp, "Attribute" character, "1" int, "0" int);

SELECT * FROM crosstab(

      'SELECT entity, timeof, status, ct

       FROM   t4

       ORDER  BY 1, 2, 3'

      ,'VALUES (1), (0)')

 AS ct (

    "Attribute" character

   ,"Section" timestamp

   ,"status_1" int

   ,"status_0" int);

--------------

CREATE TABLE pivot_test (id integer, customer_id integer, product_code VARCHAR, quantity integer);

INSERT INTO pivot_test VALUES (1, 1, 'A', 10);

INSERT INTO pivot_test VALUES (2, 1, 'B', 20);

INSERT INTO pivot_test VALUES (3, 1, 'C', 30);

INSERT INTO pivot_test VALUES (4, 2, 'A', 40);

INSERT INTO pivot_test VALUES (5, 2, 'C', 50);

INSERT INTO pivot_test VALUES (6, 3, 'A', 60);

INSERT INTO pivot_test VALUES (7, 3, 'B', 70);

INSERT INTO pivot_test VALUES (8, 3, 'C', 80);

INSERT INTO pivot_test VALUES (9, 3, 'D', 90);

INSERT INTO pivot_test VALUES (10, 4, 'A', 100);

select * from crosstab

       ('select customer_id::text,

                product_code::text,

                quantity::text

                from pivot_test

                where product_code=''A'' or product_code=''B'' or product_code=''C''

                order by 1,2'

       ) as ct(customer_id text, "A" text,"B" text,"C" text);

------------------------

CREATE TABLE student(

 student_id serial primary key,

 student_name varchar(50),

 remarks text

 );

 INSERT INTO student(student_name) VALUES('Ibrahim');

 INSERT INTO student(student_name) VALUES('Kilui');

 INSERT INTO student(student_name) VALUES('Ali Babababa');

 INSERT INTO student(student_name) VALUES('Buzi');

 CREATE TABLE subject(

 subject_id serial primary key,

 subject_name varchar(50),

 remarks text

 );

 INSERT INTO subject(subject_name) VALUES('Calculus I');

 INSERT INTO subject(subject_name) VALUES('Simulation');

 INSERT INTO subject(subject_name) VALUES('Adv Programming');

 INSERT INTO subject(subject_name) VALUES('Comm Skills');

 CREATE TABLE exam(

 exam_id serial primary key,

 exam_title varchar(50),

 student_id integer references student,

 subject_id integer references subject,

 exam_mark real default 0 not null,

 remarks text

 );

 --FINALY INSERT RESULTS

  -- INSERT INTO exam(exam_title, student_id, subject_id, exam_mark) VALUES(......); you know what to do here

 SELECT * FROM crosstab(

     'select student.student_name::text, subject.subject_name, exam.exam_mark

     from exam

     inner join student on exam.student_id = student.student_id

     inner join subject on exam.subject_id = subject.subject_id

     order by 1,2',

     'SELECT subject_name FROM subject ORDER BY 1')

 AS

   CT("Student" text, "Calculus" real, "Simulation" real, "Prog" real, "Comm Skills" real);