对于这个建议，我创建了一个简单的50行表，称为
TransPoser，在MySQL或您的数据库中可能已经有一个整数表，但是您想要类似的东西，将那些编号的列的数字从1转换为N。
然后，使用该表交叉连接到您的非规范化表（我称其为BadTable），但将其限制在第一行。然后使用一组case表达式将
pivot这些日期字符串放入一列中。如果需要的话，可以将其转换为适当的日期（我建议这样做，但未包括在内）。
然后，将这种较小的换位用作主查询中的派生表。
主查询将忽略第一行，但也使用交叉联接将所有原始行强制为50行（在本示例中为4行）。然后将这种笛卡尔积乘回到上面讨论的派生表中以提供日期。然后是另一组case表达式，它们将百分比转换为与日期和各种代码对齐的列。
结果示例（来自示例数据），手动添加了空行：
| 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 |
查询：
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` end as Percentagefrom BadTable as Bcross join (select N from TransPoser where N < 5) as Ninner join ( 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` end as theDate from BadTable as B cross join (select N from TransPoser where N < 5) as N where b.pre is null and b.Period = 'Date') as T on N.N = T.Nwhere b.pre is NOT nulland b.Period <> 'Date'order by n.n , b.pre;
对于以上内容，请参见此SQLFIDDLE
期望恕我直言，结果期望一个完全准备好的可执行文件交付确实不公平-
这是“拉伸友谊”。但是要将上面的查询转换为动态查询并不难。这有点“乏味”，因为语法有点棘手。我对MySQL并没有那么的经验，但这就是我的做法：
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.pre IS NULL ' , ' AND b.Period = ''Date'' ) AS T ON N.N = T.N WHERe b.pre IS NOT NULL AND b.Period <> ''Date'' ORDER BY n.n , b.pre ' );PREPARE stmt FROM @sqlvar;EXECUTE stmt;
[Demo of the dynamic approach](http://sqlfiddle.com/#!2/d11f7d/2)
