在MySQL中取消枢轴化多列 [英] Unpivot Multiple Columns in MySQL
问题描述
我有一些非规范化的数据，我试图将其移开，希望所有的人都可以帮助我找出执行此操作的最佳方法.我已经使用多个并集语句完成了此操作，但是我希望做的是创建一个动态查询，随着向表中添加更多列，该查询可以一遍又一遍地执行此操作.我的数据看起来像这样:(数字列一直到50)
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.
日期字段开始前的前四个NULL列是一个问题.具有数字标题(1-50)的每一列代表一个星期.问题在于，每个星期在同一列中不仅具有日期字段，而且具有该周的百分比值.我想将其向下旋转，使其看起来像这样:
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.
由不同的代码，Desc，CODE_0，期间和日期作为键.我想将日期与数字列中的百分比分开，然后将数字列带入按日期连接的自己的列中.如前所述，我已经使用UNION语句静态完成了此操作，但是我想编写某种查询，该查询可以在表扩展时动态地执行.任何帮助将不胜感激.让我知道是否需要任何其他信息，这是我关于StackOverflow的第一个问题，我有两个漂亮的屏幕截图向您展示，但是在这次交流中我还不到10个.仅适用于科幻和幻想.我知道，对吧?
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'
推荐答案
为此，我创建了一个名为TransPoser的简单的50行表，在MySQL或您的数据库中可能已经有一个整数表，但是您想要类似的东西，将那些编号的列从1到N.
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.
然后，使用该表交叉连接到非规范化表(我称其为BadTable)，但将其限制在第一行.然后使用一组case表达式，我们将那些日期字符串pivot放入一列中.如果需要的话，有可能将其转换为适当的日期(我建议这样做，但未包括在内).
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.
主查询会忽略第一行，但也会使用交叉联接将所有原始行强制为50行(在本示例中为4行).然后将这种笛卡尔积乘回到上面讨论的派生表中以提供日期.然后是另一组case表达式，它们将百分比转换为与日期和各种代码对齐的列.
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 |
查询:
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
;
有关上述内容，请参见此SQLFIDDLE
for the above see this SQLFIDDLE
由于恕我直言的问题，期望一个完全准备好的可执行文件交付确实不公平-这是延伸友谊".但是要将上面的查询转换为动态查询并不难.这有点乏味"，因为语法有点棘手.我对MySQL没有那么丰富的经验，但这就是我的做法:
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;
这篇关于在MySQL中取消枢轴化多列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
