Oracle:
-- 行传列
NOSQLDBA@prod> create table t (id int,pcode varchar2(20),datav number);
Table created.
NOSQLDBA@prod> insert into t values(1,'a1',1.1);
1 row created.
NOSQLDBA@prod> insert into t values(1,'a2',1.2);
1 row created.
NOSQLDBA@prod> insert into t values(1,'a3',1.3);
1 row created.
NOSQLDBA@prod> insert into t values(2,'a1',10.1);
1 row created.
NOSQLDBA@prod> commit;
Commit complete.
NOSQLDBA@prod> select * from t;
ID PCODE DATAV
---------- -------------------- ----------
1 a1 1.1
1 a2 1.2
1 a3 1.3
2 a1 10.1
NOSQLDBA@prod> select * from (select id,pcode,datav from t) pivot(max(datav) as dv for(pcode) in ('a1' as a1,'a2','a3'));
ID A1_DV 'a2'_DV 'a3'_DV
---------- ---------- ---------- ----------
1 1.1 1.2 1.3
2 10.1
-- 列转行
NOSQLDBA@prod>
NOSQLDBA@prod> create table tb (id int,a1 number,a2 number,a3 number);
Table created.
NOSQLDBA@prod> insert into tb values(1,1.1,1.2,1.3);
1 row created.
NOSQLDBA@prod> insert into tb values(2,10.1,null,null);
1 row created.
NOSQLDBA@prod> commit;
Commit complete.
NOSQLDBA@prod> select id,pcode,dv from tb unpivot (dv for pcode in (a1,a2,a3));
ID PC DV
---------- -- ----------
1 A1 1.1
1 A2 1.2
1 A3 1.3
2 A1 10.1
MSSQL:
-- 列传行
create table t (id int,pcode varchar(20),datav float);
insert into t values(1,'a1',1.1);
insert into t values(1,'a2',1.2);
insert into t values(1,'a3',1.3);
insert into t values(2,'a1',10.1);
select * from t pivot (max(datav) for pcode in (a1,a2,a3)) tmp;
id a1 a2 a3
1 1.1 1.2 1.3
2 10.1 NULL NULL
-- 行转列
create table tb (id int,a1 float,a2 float,a3 float);
insert into tb values(1,1.1,1.2,1.3);
insert into tb values(2,10.1,null,null);
select id,pcode,dv from tb unpivot (dv for pcode in (a1,a2,a3)) tmp;
id pcode dv
1 a1 1.1
1 a2 1.2
1 a3 1.3
2 a1 10.1
Mysql:
-- 列传行
mysql> create table t (id int,pcode varchar(20),datav double);
Query OK, 0 rows affected (0.28 sec)
mysql> insert into t values(1,'a1',1.1);
Query OK, 1 row affected (0.80 sec)
mysql> insert into t values(1,'a2',1.2);
Query OK, 1 row affected (0.05 sec)
mysql> insert into t values(1,'a3',1.3);
Query OK, 1 row affected (0.14 sec)
mysql> insert into t values(2,'a1',10.1);
Query OK, 1 row affected (0.04 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select id
-> ,max(if(pcode='a1',datav,0)) as a1_dv
-> ,max(if(pcode='a2',datav,0)) as a2_dv
-> ,max(if(pcode='a3',datav,0)) as a3_dv
-> from t
-> group by id;
+------+-------+-------+-------+
| id | a1_dv | a2_dv | a3_dv |
+------+-------+-------+-------+
| 1 | 1.1 | 1.2 | 1.3 |
| 2 | 10.1 | 0 | 0 |
+------+-------+-------+-------+
2 rows in set (0.00 sec)
Postgresql的:
CREATE TEMP TABLE foo (id int, a text, b text, c text);
INSERT INTO foo VALUES (1, 'ant', 'cat', 'chimp'), (2, 'grape', 'mint', 'basil');
-- 方法一:使用UNION ALL来“取消透视”或“取消交叉表”:
SELECT id,
'a' AS colname,
a AS thing
FROM foo
UNION ALL
SELECT id,
'b' AS colname,
b AS thing
FROM foo
UNION ALL
SELECT id,
'c' AS colname,
c AS thing
FROM foo
ORDER BY id;
--这会在上运行3个不同的子查询foo,每个要取消透视的列一个,然后在一个表中返回每个子查询中的每个记录。
--但这将扫描表N次,其中N是您要取消透视的列数。这效率低下,而且是一个很大的问题,例如,当您使用非常大的表进行扫描时,这需要很长时间。
--方法二: 使用:
SELECT id, unnest(array['a', 'b', 'c']) AS colname,unnest(array[a, b, c]) AS thing
FROM foo
ORDER BY id;
--其中:array[a, b, c]返回一个数组对象,其值分别为a,b和c。 unnest(array[a, b, c])将数组的每个元素的结果分成一行
通用的:
-- 列传行
NOSQLDBA@prod> select * from t;
ID PCODE DATAV
---------- -------------------- ----------
1 a1 1.1
1 a2 1.2
1 a3 1.3
2 a1 10.1
NOSQLDBA@prod> select id,
2 max(decode(pcode,'a1',datav,0)) a1_dv,
3 max(decode(pcode,'a2',datav,0)) a2_dv,
4 max(decode(pcode,'a3',datav,0)) a3_dv
5 from t
6 group by id;
ID A1_DV A2_DV A3_DV
---------- ---------- ---------- ----------
1 1.1 1.2 1.3
2 10.1 0 0
NOSQLDBA@prod>
-- 行转列
NOSQLDBA@prod> select * from tb;
ID A1 A2 A3
---------- ---------- ---------- ----------
1 1.1 1.2 1.3
2 10.1
NOSQLDBA@prod> select id,'A1',a1
2 from tb
3 union all
4 select id,'A2',a2
5 from tb
6 union all
7 select id,'A3',a3
8 from tb;
ID 'A A1
---------- -- ----------
1 A1 1.1
2 A1 10.1
1 A2 1.2
2 A2
1 A3 1.3
2 A3
6 rows selected.