Unpivot Multiple Columns in MySQL

Question

I've got some non-normalized data that I'm trying to unpivot the columns on, I'm hoping you all can help me figure out the best way to do this. I've done this using multiple union statements, but what I'm hoping to do is create a dynamic query that can do this over and over as more columns are added to the table. My data looks something like this: (Numerical columns go all the way through 50)

| Code  | Desc  | Code_0 | Desc_0| Period|    1    |    2   |    3    |    4     |  
|-------|-------|--------|-------|-------|---------|--------|---------|----------|
| NULL  | NULL  |  NULL  |  NULL |  Date |29-Nov-13|6-Dec-13|13-Dec-13| 20-Dec-13|  
|CTR07  |Risk   |  P1    | Phase1|  P    |   0.2   |  0.4   |   0.6   |    1.1   |         
|CTR07  |Risk   |  P1    | Phase1|  F    |   0.2   |  0.4   |   0.6   |    1.1   |          
|CTR07  |Risk   |  P1    | Phase1|  A    |   0.2   |  0.4   |   0.6   |    1.1   |
|CTR08  |Oper   |  P1    | Phase1|  P    |   0.6   |  0.6   |   0.9   |    2.7   |
|CTR08  |Oper   |  P1    | Phase1|  F    |   0.6   |  0.6   |   0.9   |    2.7   |
|CTR08  |Oper   |  P1    | Phase1|  A    |   0.6   |  0.6   |   0.9   |    2.7   |

Column Headers are the top Row. As you can see looking at the data, there are some oddities that need to be dealth with.

The first four NULL columns before the date fields start are a problem. Each column that has a numerical header (1-50) represents one week. The problem with that is that each week has not only the date field, but also the percentage values for that week in the same column. I'd like to get it pivoted down so it looks something like this:

| Code  | Desc  |Code_0 |Desc_0 | Period| Date    |Percent|  
|-------|-------|-------|-------|-------|---------|-------|
|CTR07  | Risk  |  P1   | Phase |   P   | 11/29/13| 0.2   |   
|CTR07  | Risk  |  P1   | Phase1|   F   | 11/29/13| 0.2   |
|CTR07  | Risk  |  P1   | Phase1|   A   | 11/29/13| 0.2   |
|CTR08  | Oper. |  P1   | Phase1|   P   | 11/29/13| 0.6   |

With each week's date in it's own column, and percentages grouped by their respective dates.

Keyed by distinct Code, Desc, CODE_0, Period and Date. I'd like to Separate the dates from the percentages that are in the numerical columns, then bring the numerical columns into their own column connected by date. As I said before, I've done it statically with UNION statements, but I'd like to write some kind of query that does it dynamically as the table expands. Any help would be greatly appreciated. Let me know if you need any additional information, this is my first question on StackOverflow, and I had two nice screenshots to show you, but I'm not up to 10 yet on this exchange. Only on Sci-Fi and Fantasy. I know right?

Code that I used in the union to statically create the bottom table:

select `Code`, `Desc`, `Code_0`, `Desc_0`, `Period`, (select STR_TO_DATE(`1`, '%d%b%y') from combined_complete where `1` = '29Nov13') as `Date`, `1` as `Percent`
from combined_complete
where period <> 'Date'
union
select `Code`, `Desc`, `Code_0`, `Desc_0`, `Period`, (select STR_TO_DATE(`2`, '%d%b%y') from combined_complete where `2` = '06Dec13') as `Date`, `2`
from combined_complete
where period <> 'Date'
union
select `Code`, `Desc`, `Code_0`, `Desc_0`, `Period`, (select STR_TO_DATE(`3`, '%d%b%y') from combined_complete where `3` = '13Dec13') as `Date`, `3`
from combined_complete
where period <> 'Date'
union
select `Code`, `Desc`, `Code_0`, `Desc_0`, `Period`, (select STR_TO_DATE(`4`, '%d%b%y') from combined_complete where `4` = '20Dec13') as `Date`, `4`
from combined_complete
where period <> 'Date'

Honestly, I would'nt try to do this with "pure" SQL... I'd rather use another programming language (python, java, etc.) to read the data and store it in a new table — Barranka
– Barranka, Commented Aug 15, 2014 at 0:21
in any case don't use UNION please use UNION ALL, union by itself is not needed here and is slower then union all — Paul Maxwell
– Paul Maxwell, Commented Aug 15, 2014 at 11:27

Paul Maxwell · Accepted Answer · 2014-08-19 00:29:27Z

For this suggestion I have created a simple 50 row table called TransPoser, there may already be a table of integers available in MySQL or in your db, but you want something similar that will give your number 1 to N for those numbered columns.

Then, using that table, cross join to your non-normalized table (I call it BadTable) but restrict this to the first row. Then using a set of case expressions we pivot those date strings into a column. It would be possible to convert to a proper date as we do this if needed (I would suggest it, but haven't included it).

This small transposition is then used as a derived table in the main query.

The main query ignores that first row, but also uses a cross join to force all original rows into the 50 rows (or 4 as we see in this example). This Cartesian product is then joined back to the derived table discussed above to supply the dates. Then it is another set of case expressions that transpose the percentages into a column, aligned to the date and various codes.

Example result (from sample data), blank lines added manually:

| N |  CODE | DESC | CODE_0 | DESC_0 |   THEDATE | PERCENTAGE |
|---|-------|------|--------|--------|-----------|------------|
| 1 | CTR07 | Risk |     P1 | Phase1 | 29-Nov-13 |        0.2 |
| 1 | CTR07 | Risk |     P1 | Phase1 | 29-Nov-13 |        0.2 |
| 1 | CTR07 | Risk |     P1 | Phase1 | 29-Nov-13 |        0.2 |
| 1 | CTR08 | Oper |     P1 | Phase1 | 29-Nov-13 |        0.6 |
| 1 | CTR08 | Oper |     P1 | Phase1 | 29-Nov-13 |        0.6 |
| 1 | CTR08 | Oper |     P1 | Phase1 | 29-Nov-13 |        0.6 |

| 2 | CTR07 | Risk |     P1 | Phase1 |  6-Dec-13 |        0.4 |
| 2 | CTR07 | Risk |     P1 | Phase1 |  6-Dec-13 |        0.4 |
| 2 | CTR07 | Risk |     P1 | Phase1 |  6-Dec-13 |        0.4 |
| 2 | CTR08 | Oper |     P1 | Phase1 |  6-Dec-13 |        0.6 |
| 2 | CTR08 | Oper |     P1 | Phase1 |  6-Dec-13 |        0.6 |
| 2 | CTR08 | Oper |     P1 | Phase1 |  6-Dec-13 |        0.6 |

| 3 | CTR07 | Risk |     P1 | Phase1 | 13-Dec-13 |        0.6 |
| 3 | CTR07 | Risk |     P1 | Phase1 | 13-Dec-13 |        0.6 |
| 3 | CTR07 | Risk |     P1 | Phase1 | 13-Dec-13 |        0.6 |
| 3 | CTR08 | Oper |     P1 | Phase1 | 13-Dec-13 |        0.9 |
| 3 | CTR08 | Oper |     P1 | Phase1 | 13-Dec-13 |        0.9 |
| 3 | CTR08 | Oper |     P1 | Phase1 | 13-Dec-13 |        0.9 |

| 4 | CTR07 | Risk |     P1 | Phase1 | 20-Dec-13 |        1.1 |
| 4 | CTR07 | Risk |     P1 | Phase1 | 20-Dec-13 |        1.1 |
| 4 | CTR07 | Risk |     P1 | Phase1 | 20-Dec-13 |        1.1 |
| 4 | CTR08 | Oper |     P1 | Phase1 | 20-Dec-13 |        2.7 |
| 4 | CTR08 | Oper |     P1 | Phase1 | 20-Dec-13 |        2.7 |
| 4 | CTR08 | Oper |     P1 | Phase1 | 20-Dec-13 |        2.7 |

The query:

select
       n.n
     , b.Code
     , b.Desc
     , b.Code_0
     , b.Desc_0
     , T.theDate
     , case
            when n.n =  1 then `1`
            when n.n =  2 then `2`
            when n.n =  3 then `3`
            when n.n =  4 then `4`
         /* when n.n =  5 then `5` */
         /* when n.n = 50 then `50`*/
       end as Percentage
from BadTable as B
cross join (select N from TransPoser where N < 5) as N
inner join (
            /* transpose just the date row */
            /* join back vis the number given to each row */
            select
                    n.n
                  , case
                        when n.n =  1 then `1`
                        when n.n =  2 then `2`
                        when n.n =  3 then `3`
                        when n.n =  4 then `4`
                     /* when n.n =  5 then `5` */
                     /* when n.n = 50 then `50`*/
                   end as theDate
            from BadTable as B
            cross join (select N from TransPoser where N < 5) as N
            where b.code is null
            and b.Period = 'Date'
           ) as T on N.N = T.N
where b.code is NOT null
and b.Period <> 'Date'
order by
       n.n
     , b.code
;

for the above see this SQLFIDDLE

It really isn't fair to expect a fully prepared executable deliverable as the result of a question IMHO - it is "stretching the friendship". But to morph the above query into a dynamic query isn't too hard. it's just a bit "tedious" as the syntax is a bit tricky. I'm not that experienced with MySQL but this is how I would do it:

set @numcols := 4;
set @casevar := '';

set @casevar := (
                  select 
                  group_concat(@casevar
                                       ,'when n.n =  '
                                       , n.n
                                       ,' then `'
                                       , n.n
                                       ,'`'
                                      SEPARATOR ' ')
                  from TransPoser as n
                  where n.n <= @numcols
                 )
;


set @sqlvar := concat(
          'SELECT n.n , b.Code , b.Desc , b.Code_0 , b.Desc_0 , T.theDate , CASE '
        , @casevar
        , ' END AS Percentage FROM BadTable AS B CROSS JOIN (SELECT N FROM  TransPoser WHERE N <='
        , @numcols
        , ') AS N INNER JOIN ( SELECT n.n , CASE '
        , @casevar                                                                                                       
        , ' END AS theDate FROM BadTable AS B CROSS JOIN (SELECT N FROM  TransPoser WHERE N <='
        , @numcols
        , ') AS N WHERE b.code IS NULL '
        , ' AND b.Period = ''Date'' ) AS T ON N.N = T.N WHERE b.code IS NOT NULL AND b.Period <> ''Date'' ORDER BY n.n , b.code ' 
        );

PREPARE stmt FROM @sqlvar;
EXECUTE stmt;

Demo of the dynamic approach

This is a great answer, however it still limits me to 50 rows. Is there a way to make this dynamic so that if more than 50 columns are coming from the source that it will unpivot the additional columns as well? This is REALLY close to what I'm looking for. It's just the dynamic problem.
re-read your question then "(Numerical columns go all the way through 50)" if you needed dynamic beyond 50 why state 50 as a fixed number?
The point is, yes, it can be made dynamic but I'm not here to write the entire solution for you. The technique requested to unpivot I have provided. Have you never written any "dynamic sql"?
I really appreciate it. I did mention in the question that I had done the non-dynamic version before with union statements, which is why I was looking for a dynamic solution. Your answer is definitely the best so I'm giving you full credit for being awesome. Thanks again for writing it out for me, I know SQL well, but dynamic or PL/SQL is something that I don't have much experience with.

Adrian0CG · Accepted Answer · 2014-08-15 00:56:13Z

0

One rough way to go would be:

Retrieve Columnames from INFORMATION_SCHEMA.COLUMNS of the table, either by a LIKE-Pattern or ORDINAL_POSITION > 5
For each of the columns execute a prepared statement that inserts the first five cols and the number col into a new table
during insert, also sub-select the value for the date column by the NULL values

answered Aug 15, 2014 at 0:56

Adrian0CG

661 silver badge3 bronze badges

1 Comment

masdawg Over a year ago

Do you have some examples of code to do the above? I know the theory behind what you're suggesting, I just have a hard time visualizing how it would come together. Thanks in advance.

Collectives™ on Stack Overflow

Unpivot Multiple Columns in MySQL

2 Answers 2

5 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related