PostgreSQL - tabla pivote ejemplo 3

   
Vista:
Imágen de perfil de outrera

tabla pivote ejemplo 3

Publicado por outrera orlandoutrera@gmail.com (35 intervenciones) el 16/04/2014 23:02:07
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
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);
Valora esta pregunta
Me gusta: Está pregunta es útil y esta claraNo me gusta: Está pregunta no esta clara o no es útil
0
Responder