Gather several column using value as number of new rows

Question

I want to convert a somewhat dirty table into a normalized one. The structure of the table is as follow:

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
);

Somewhat similar to this question but with a caveat, I have a bit/integer instead for values in a true/false fashion, bit columns can contain values 0 and 1, and the bi column any positive number and 0. I want to create a new row keeping name and date column and the name of the non zero column. Something like this:

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   |

As can be seen, the bi column can appear several times if the value is >1. Others, unless there's another row, are likely to have only one combination of date, name and proc column, as seen in this excerpt of 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

I was trying to use case statements but that only creates a single row. How can I create multiple rows from one record using the value as number of new rows to create? I prefer using generic SQL, but I'm using MariaDB.

score 1 · Accepted Answer · answered Nov 30 '17 at 17:40

The simplest method is probably 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;

That the multiple rows for bi.

Gather several column using value as number of new rows

1 Answers1