· 8 years ago · Jun 13, 2018, 11:34 PM
1select
2 ifnull(x.form_datetime,'') reg_date,
3 ifnull(x.property_value,'') amg_id,
4 x.form_number,
5 x.form_name,
6 x.form_version,
7 ifnull(t1.property_value,'') first_name,
8 ifnull(t2.property_value,'') last_name,
9 ifnull(t3.property_value,'') address,
10 ifnull(t4.property_value,'') address_2,
11 ifnull(t5.property_value,'') city,
12 ifnull(t6.property_value,'') state_code,
13 ifnull(t7.property_value,'') zip,
14 ifnull(t8.property_value,'') phone,
15 ifnull(t9.property_value,'') email,
16 ifnull(t10.property_value,'') registrant_type,
17 t11.property_value auth_type_code
18from
19 (select distinct form_datetime, form_number, form_name, form_version, property_value from c_regs where property_name = 'field.frm_personID') as x
20 inner join (select distinct * from c_regs) as t1 on t1.form_number = x.form_number and t1.property_name = 'field.frm_firstName'
21 inner join (select distinct * from c_regs) as t2 on t2.form_number = x.form_number and t2.property_name = 'field.frm_lastName'
22 inner join (select distinct * from c_regs) as t3 on t3.form_number = x.form_number and t3.property_name = 'field.frm_address'
23 left join (select distinct * from c_regs) as t4 on t4.form_number = x.form_number and t4.property_name = 'field.frm_address2'
24 inner join (select distinct * from c_regs) as t5 on t5.form_number = x.form_number and t5.property_name = 'field.frm_city'
25 inner join (select distinct * from c_regs) as t6 on t6.form_number = x.form_number and t6.property_name = 'field.frm_state'
26 inner join (select distinct * from c_regs) as t7 on t7.form_number = x.form_number and t7.property_name = 'field.frm_zip'
27 inner join (select distinct * from c_regs) as t8 on t8.form_number = x.form_number and t8.property_name = 'field.frm_phone'
28 inner join (select distinct * from c_regs) as t9 on t9.form_number = x.form_number and t9.property_name = 'field.frm_emailAddress'
29 left join (select distinct * from c_regs) as t10 on t10.form_number = x.form_number and t10.property_name = 'field.frm_youAre'
30 inner join (select distinct * from c_regs) as t11 on t11.form_number = x.form_number and t11.property_name = 'field.frm_authType'
31;
32
33SELECT form_number,
34 MAX(form_datetime) AS reg_date,
35 MAX(form_name) AS form_name,
36 MAX(form_version) AS form_version,
37 MAX(CASE property_name WHEN 'field.frm_personID' THEN property_value END) AS amg_id,
38 MAX(CASE property_name WHEN 'field.frm_firstName' THEN property_value END) AS first_name,
39 MAX(CASE property_name WHEN 'field.frm_lastName' THEN property_value END) AS last_name,
40 MAX(CASE property_name WHEN 'field.frm_address' THEN property_value END) AS address,
41 MAX(CASE property_name WHEN 'field.frm_address2' THEN property_value END) AS address_2,
42 MAX(CASE property_name WHEN 'field.frm_city' THEN property_value END) AS city,
43 MAX(CASE property_name WHEN 'field.frm_state' THEN property_value END) AS state_code,
44 MAX(CASE property_name WHEN 'field.frm_zip' THEN property_value END) AS zip,
45 MAX(CASE property_name WHEN 'field.frm_phone' THEN property_value END) AS phone,
46 MAX(CASE property_name WHEN 'field.frm_emailAddress' THEN property_value END) AS email,
47 MAX(CASE property_name WHEN 'field.frm_youAre' THEN property_value END) AS registrant_type,
48 MAX(CASE property_name WHEN 'field.frm_authType' THEN property_value END) AS auth_type_code
49FROM c_regs
50GROUP BY form_number;
51
52DROP TABLE IF EXISTS my_table;
53
54CREATE TABLE my_table
55(entity INT NOT NULL
56,attribute INT NOT NULL
57,value INT NOT NULL
58,PRIMARY KEY(entity,attribute)
59);
60
61INSERT INTO my_table VALUES
62(1,101,RAND()*100),
63(1,102,RAND()*100),
64(1,103,RAND()*100),
65(1,104,RAND()*100),
66(1,105,RAND()*100),
67(1,106,RAND()*100),
68(1,107,RAND()*100),
69(1,108,RAND()*100),
70(1,109,RAND()*100),
71(1,110,RAND()*100),
72(1,111,RAND()*100),
73(1,112,RAND()*100);
74
75INSERT IGNORE INTO my_table SELECT i4.i*1000+i3.i*100+i2.i*10+i1.i+1, attribute, RAND()*100 FROM my_table,ints i1, ints i2, ints i3, ints i4;
76
77SELECT SQL_NO_CACHE a.entity
78 , MAX(CASE WHEN attribute = 101 THEN value END) x101
79 , MAX(CASE WHEN attribute = 102 THEN value END) x102
80 , MAX(CASE WHEN attribute = 103 THEN value END) x103
81 , MAX(CASE WHEN attribute = 104 THEN value END) x104
82 , MAX(CASE WHEN attribute = 105 THEN value END) x105
83 , MAX(CASE WHEN attribute = 106 THEN value END) x106
84 , MAX(CASE WHEN attribute = 107 THEN value END) x107
85 , MAX(CASE WHEN attribute = 108 THEN value END) x108
86 , MAX(CASE WHEN attribute = 109 THEN value END) x109
87 , MAX(CASE WHEN attribute = 110 THEN value END) x110
88 , MAX(CASE WHEN attribute = 111 THEN value END) x111
89 , MAX(CASE WHEN attribute = 112 THEN value END) x112
90 FROM my_table a
91 GROUP
92 BY a.entity;
93
94+--------+------+------+------+------+------+------+------+------+------+------+------+------+
95| entity | x101 | x102 | x103 | x104 | x105 | x106 | x107 | x108 | x109 | x110 | x111 | x112 |
96+--------+------+------+------+------+------+------+------+------+------+------+------+------+
97| 1 | 78 | 8 | 4 | 95 | 66 | 43 | 16 | 51 | 9 | 89 | 20 | 33 |
98...
99| 9998 | 61 | 72 | 67 | 20 | 23 | 10 | 31 | 37 | 69 | 18 | 24 | 32 |
100| 9999 | 67 | 91 | 32 | 58 | 77 | 81 | 61 | 22 | 75 | 65 | 91 | 42 |
101| 10000 | 52 | 38 | 56 | 32 | 14 | 77 | 10 | 99 | 70 | 70 | 82 | 13 |
102+--------+------+------+------+------+------+------+------+------+------+------+------+------+
10310000 rows in set (0.20 sec)
104
105SELECT SQL_NO_CACHE a.entity
106 , a.value x101
107 , b.value x102
108 , c.value x103
109 , d.value x104
110 , e.value x105
111 , f.value x106
112 , g.value x107
113 , h.value x108
114 , i.value x109
115 , j.value x110
116 , k.value x111
117 , l.value x112
118 FROM my_table a
119 LEFT JOIN my_table b ON b.entity = a.entity AND b.attribute = 102
120 LEFT JOIN my_table c ON c.entity = a.entity AND c.attribute = 103
121 LEFT JOIN my_table d ON d.entity = a.entity AND d.attribute = 104
122 LEFT JOIN my_table e ON e.entity = a.entity AND e.attribute = 105
123 LEFT JOIN my_table f ON f.entity = a.entity AND f.attribute = 106
124 LEFT JOIN my_table g ON g.entity = a.entity AND g.attribute = 107
125 LEFT JOIN my_table h ON h.entity = a.entity AND h.attribute = 108
126 LEFT JOIN my_table i ON i.entity = a.entity AND i.attribute = 109
127 LEFT JOIN my_table j ON j.entity = a.entity AND j.attribute = 110
128 LEFT JOIN my_table k ON k.entity = a.entity AND k.attribute = 111
129 LEFT JOIN my_table l ON l.entity = a.entity AND l.attribute = 112
130 WHERE a.attribute = 101;
131
132+--------+------+------+------+------+------+------+------+------+------+------+------+------+
133| entity | x101 | x102 | x103 | x104 | x105 | x106 | x107 | x108 | x109 | x110 | x111 | x112 |
134+--------+------+------+------+------+------+------+------+------+------+------+------+------+
135| 1 | 78 | 8 | 4 | 95 | 66 | 43 | 16 | 51 | 9 | 89 | 20 | 33 |
136...
137| 9998 | 61 | 72 | 67 | 20 | 23 | 10 | 31 | 37 | 69 | 18 | 24 | 32 |
138| 9999 | 67 | 91 | 32 | 58 | 77 | 81 | 61 | 22 | 75 | 65 | 91 | 42 |
139| 10000 | 52 | 38 | 56 | 32 | 14 | 77 | 10 | 99 | 70 | 70 | 82 | 13 |
140+--------+------+------+------+------+------+------+------+------+------+------+------+------+
14110000 rows in set (0.23 sec)
142
143| 4998 | 59 | 55 | 93 | 48 | 72 | 32 | 38 | 36 | 6 | 82 | 23 | 62 |
144 | 4999 | 23 | 10 | 11 | 29 | 69 | 67 | 92 | 72 | 25 | 49 | 79 | 48 |
145 | 5000 | 39 | 86 | 77 | 0 | 30 | 38 | 48 | 54 | 9 | 97 | 25 | 54 |
146 +--------+------+------+------+------+------+------+------+------+------+------+------+------+
147 5000 rows in set (0.12 sec)
148
149| 4998 | 59 | 55 | 93 | 48 | 72 | 32 | 38 | 36 | 6 | 82 | 23 | 62 |
150 | 4999 | 23 | 10 | 11 | 29 | 69 | 67 | 92 | 72 | 25 | 49 | 79 | 48 |
151 | 5000 | 39 | 86 | 77 | 0 | 30 | 38 | 48 | 54 | 9 | 97 | 25 | 54 |
152 +--------+------+------+------+------+------+------+------+------+------+------+------+------+
153 5000 rows in set (0.11 sec)
154
155SELECT
156 ifnull(x.form_datetime,'') reg_date,
157 ifnull(x.property_value,'') amg_id,
158 x.form_number,
159 x.form_name,
160 x.form_version,
161 x.property_name,
162 x.property_value
163FROM c_regs x
164WHERE x.property_name IN (
165 'field.frm_firstName',
166 'field.frm_lastName',
167 'field.frm_address',
168 ...
169)
170AND x.form_number = 'the form id'
171GROUP BY x.form_number, x.property_name
172ORDER BY x.form_number ASC;
173
174SELECT ID, NAME, AMOUNT, DATE
175FROM CUSTOMERS
176LEFT JOIN ORDERS
177ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
178UNION
179SELECT ID, NAME, AMOUNT, DATE
180FROM CUSTOMERS
181RIGHT JOIN ORDERS
182ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;
183
184inner join (select distinct * from c_regs) as t7
185 on t7.form_number = x.form_number and t7.property_name = 'field.frm_zip'
186
187inner join c_regs AS t7 ON
188 t7.form_number = x.form_number and t7.property_name = 'field.frm_zip'