Cross tabulations
От | Muhyiddin A.M Hayat |
---|---|
Тема | Cross tabulations |
Дата | |
Msg-id | 012501c4b65f$9b70b620$4f00a8c0@middinks обсуждение исходный текст |
Ответы |
Re: Cross tabulations
|
Список | pgsql-sql |
Dear all,
I need to do something similar to a cross tabulation, but without any
aggregation.
I have below table
id | employee_id | state | check_time
----+-------------+-----------+--------------------
21 | 1 | In | 2004-10-12 21:37:13
22 | 1 | Break Out | 2004-10-12 21:37:31
23 | 1 | Break In | 2004-10-12 21:37:42
24 | 1 | Out | 2004-10-12 21:37:50
25 | 1 | In | 2004-10-13 19:20:36
26 | 1 | In | 2004-10-14 01:33:48
27 | 1 | Break Out | 2004-10-14 01:59:15
28 | 1 | Break In | 2004-10-14 03:15:45
29 | 1 | Out | 2004-10-14 03:17:23
30 | 3 | In | 2004-10-14 03:17:43
31 | 3 | Break Out | 2004-10-14 19:32:34
32 | 2 | In | 2004-10-14 20:34:15
33 | 3 | In | 2004-10-15 02:01:28
34 | 3 | Break Out | 2004-10-15 02:02:07
35 | 3 | In | 2004-10-16 02:06:43
36 | 1 | In | 2004-10-16 02:07:33
37 | 1 | Break Out | 2004-10-16 02:09:09
38 | 1 | Break In | 2004-10-16 04:10:21
39 | 1 | Out | 2004-10-16 04:12:27
40 | 3 | Break Out | 2004-10-16 21:38:22
I need to do something similar to a cross tabulation, but without any
aggregation.
I have below table
id | employee_id | state | check_time
----+-------------+-----------+--------------------
21 | 1 | In | 2004-10-12 21:37:13
22 | 1 | Break Out | 2004-10-12 21:37:31
23 | 1 | Break In | 2004-10-12 21:37:42
24 | 1 | Out | 2004-10-12 21:37:50
25 | 1 | In | 2004-10-13 19:20:36
26 | 1 | In | 2004-10-14 01:33:48
27 | 1 | Break Out | 2004-10-14 01:59:15
28 | 1 | Break In | 2004-10-14 03:15:45
29 | 1 | Out | 2004-10-14 03:17:23
30 | 3 | In | 2004-10-14 03:17:43
31 | 3 | Break Out | 2004-10-14 19:32:34
32 | 2 | In | 2004-10-14 20:34:15
33 | 3 | In | 2004-10-15 02:01:28
34 | 3 | Break Out | 2004-10-15 02:02:07
35 | 3 | In | 2004-10-16 02:06:43
36 | 1 | In | 2004-10-16 02:07:33
37 | 1 | Break Out | 2004-10-16 02:09:09
38 | 1 | Break In | 2004-10-16 04:10:21
39 | 1 | Out | 2004-10-16 04:12:27
40 | 3 | Break Out | 2004-10-16 21:38:22
I need something like this:
date | employee_id | in | break_out | break_id | out
-----------+-------------+----------+-----------+----------+----------
2004-10-12 | 1 | 21:37:13 | 21:37:31 | 21:37:42 |21:37:50
2004-10-14 | 1 | 01:33:48 | 01:59:15 | 03:15:45 |03:17:23
2004-10-14 | 3 | 03:17:43 | 19:32:34 | 03:15:45 |03:17:23
........
........
........
-----------+-------------+----------+-----------+----------+----------
2004-10-12 | 1 | 21:37:13 | 21:37:31 | 21:37:42 |21:37:50
2004-10-14 | 1 | 01:33:48 | 01:59:15 | 03:15:45 |03:17:23
2004-10-14 | 3 | 03:17:43 | 19:32:34 | 03:15:45 |03:17:23
........
........
........
В списке pgsql-sql по дате отправления: