行转列和列传行

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.