如果表格和列名一致,你应该能够通过日期算术来确定每个最终行的日期,只需要为每个表格使用一个日期字面量,例如“mytable2011”的表格使用“2011-01-01”。
大多数“反转”操作是使用JSON进行的,首先将每个源行放入JSON中,然后从中创建行,如下所示。
SQL Fiddle
PostgreSQL 9.3 Schema Setup:
CREATE TABLE MyTable2011
("cell" int, "day1" numeric, "day2" numeric, "day3" int, "day4" numeric, "day365" int)
//
INSERT INTO MyTable2011
("cell", "day1", "day2", "day3", "day4", "day365")
VALUES
(1, 3.7167, 0.00, 0.00, 0.1487, 0.3256),
(2, 0, 0, 0.2331, 0.1461, 1.8765),
(3, 1.431, 0.4121, 0, 1.4321, 0.00)
//
查询1:
SELECT row_to_json(MyTable2011) as jstring FROM MyTable2011
结果:
| jstring |
|
| {"cell":1,"day1":3.7167,"day2":0.00,"day3":0,"day4":0.1487,"day365":0} |
| {"cell":2,"day1":0,"day2":0,"day3":0,"day4":0.1461,"day365":2} |
| {"cell":3,"day1":1.431,"day2":0.4121,"day3":0,"day4":1.4321,"day365":0} |
查询 2:
SELECT
jstring->>'cell' as cell
, json_each_text(jstring) as pairs
FROM (
SELECT
row_to_json(MyTable2011) as jstring
FROM MyTable2011
) as jrows
"
结果:
"
| cell | pairs |
|------|---------------|
| 1 | (cell,1) |
| 1 | (day1,3.7167) |
| 1 | (day2,0.00) |
| 1 | (day3,0) |
| 1 | (day4,0.1487) |
| 1 | (day365,0) |
| 2 | (cell,2) |
| 2 | (day1,0) |
| 2 | (day2,0) |
| 2 | (day3,0) |
| 2 | (day4,0.1461) |
| 2 | (day365,2) |
| 3 | (cell,3) |
| 3 | (day1,1.431) |
| 3 | (day2,0.4121) |
| 3 | (day3,0) |
| 3 | (day4,1.4321) |
| 3 | (day365,0) |
查询3:
SELECT
date '2011-01-01' + CAST(REPLACE((pairs).key,'day','') as integer) -1 as thedate
, CAST(REPLACE((pairs).key,'day','') as integer) as daynum
, cell
, (pairs).value as thevalue
FROM (
SELECT
jstring->>'cell' as cell
, json_each_text(jstring) as pairs
FROM (
SELECT
row_to_json(MyTable2011) as jstring
FROM MyTable2011
) as jrows
) as unpiv
WHERE (pairs).key <> 'cell'
结果:
| thedate | daynum | cell | thevalue |
|----------------------------|--------|------|----------|
| January, 01 2011 00:00:00 | 1 | 1 | 3.7167 |
| January, 02 2011 00:00:00 | 2 | 1 | 0.00 |
| January, 03 2011 00:00:00 | 3 | 1 | 0 |
| January, 04 2011 00:00:00 | 4 | 1 | 0.1487 |
| December, 31 2011 00:00:00 | 365 | 1 | 0 |
| January, 01 2011 00:00:00 | 1 | 2 | 0 |
| January, 02 2011 00:00:00 | 2 | 2 | 0 |
| January, 03 2011 00:00:00 | 3 | 2 | 0 |
| January, 04 2011 00:00:00 | 4 | 2 | 0.1461 |
| December, 31 2011 00:00:00 | 365 | 2 | 2 |
| January, 01 2011 00:00:00 | 1 | 3 | 1.431 |
| January, 02 2011 00:00:00 | 2 | 3 | 0.4121 |
| January, 03 2011 00:00:00 | 3 | 3 | 0 |
| January, 04 2011 00:00:00 | 4 | 3 | 1.4321 |
| December, 31 2011 00:00:00 | 365 | 3 | 0 |