使用值作为新行数来收集几列

Question

我想将一个有点脏的表转换为归一化的表。表的结构如下：

CREATE TABLE dirty_table(
   date            DATE  NOT NULL
  ,name            VARCHAR(24) NOT NULL
  ,co              BIT  NOT NULL
  ,en              BIT  NOT NULL
  ,re              BIT  NOT NULL
  ,po              BIT  NOT NULL
  ,ga              BIT  NOT NULL
  ,pr              BIT  NOT NULL
  ,bi              INTEGER  NOT NULL
);

与此问题有些相似，但是有一个警告，我有一个位/整数，而是以真实/错误的方式进行值，位列可以包含值0和1，而BI列有任何正数和0。我想要创建一个新的行姓名和日期列以及非零列的名称。这样的东西：

date       |name      |proc |
-----------|----------|-----|
2017-07-04 |Jonny doe |bi   |
2017-07-04 |Jonny doe |bi   |
2017-07-07 |Jonny doe |ga   |
2017-07-04 |Jonny doe |po   |
2017-07-04 |Jonda doe |en   |
2017-07-04 |Jonda doe |co   |
2017-07-07 |Jonda doe |re   |
2017-07-07 |Jonda doe |re   |
2017-08-03 |Jonda doe |re   |
2017-08-08 |Josep doe |en   |
2017-08-09 |Josep doe |bi   |
2017-08-11 |Josep doe |ga   |

可以看出，如果值为> 1，则BI列可以出现多次。其他，除非有另一行，否则可能只有一个日期，姓名和proc列的组合，如dirty_table的摘录所示：

：

date        name    co  en  re  po  ga  pr  bi
2017-07-03  DPSUW   1   1   0   0   0   0   2
2017-07-03  XDUPT   1   0   0   0   0   0   0
2017-07-03  XIYUD   0   1   0   0   0   0   1
2017-07-03  HBJRL   1   1   0   0   0   0   2
2017-07-03  DIHMP   1   1   0   0   0   0   1
2017-07-04  MTHDT   1   1   0   0   0   0   2
2017-07-04  MFPLI   0   1   0   0   0   0   1
2017-07-04  GKHFG   1   0   0   0   0   0   1
2017-07-04  QKDNE   1   1   0   0   0   0   2
2017-07-04  GSXLN   1   1   0   0   0   0   2
2017-07-05  ICKUT   0   1   0   0   0   0   1
2017-07-05  NHVLT   0   1   0   0   0   0   1
2017-07-05  KTSFX   1   1   0   0   0   0   1
2017-07-05  AINSA   1   1   0   0   0   0   2
2017-07-07  YUCAU   0   1   0   0   0   0   1
2017-07-07  YLLVX   1   0   0   0   0   0   1
2017-07-10  CSIMK   1   1   0   0   0   0   2
2017-07-10  PWNCV   0   1   0   0   0   0   1
2017-07-10  AMMVX   0   1   0   0   0   0   1
2017-07-11  BLELT   0   1   0   0   0   0   1
2017-07-11  ONAKD   0   1   0   0   0   0   1
2017-07-11  IGJDK   1   0   0   0   0   0   1
2017-07-11  TOQLH   1   1   0   0   0   0   2
2017-07-11  DUQWM   1   0   0   0   0   0   0
2017-07-11  SFWVP   1   1   0   0   0   0   2
2017-07-12  MQVHW   0   1   0   0   0   0   1
2017-07-12  OFHWQ   0   1   0   0   0   0   1
2017-07-12  MPOAK   1   1   0   0   0   0   1
2017-07-12  YPFEH   1   1   0   0   0   0   1
2017-07-12  XUENE   1   0   0   0   0   0   1

我试图使用案例语句，但这仅创建一个行。如何使用作为新行的数量创建的值创建一个记录的多行？我更喜欢使用通用SQL，但是我正在使用Mariadb。

Answer 1

最简单的方法可能是 union all：

select date, name, 'co' as proc from t where co >= 1 union all
select date, name, 'en' as proc from t where en >= 1 union all
. . .
select date, name, 'bi' as proc from t where bi >= 1 union all
select date, name, 'bi' as proc from t where bi >= 2;

bi的多行。