MySQL:将列转换为行 [英] MySQL: Convert column into rows

问题描述

我有成千上万的列需要转换成行.

I have a zillion of colums that I need to convert into rows.

我有3个主要类别(CAT，ODB和GPA) 每个类别都有一个月(从一月到十二月)，每个类别都有一年) 这是该结构的示例:

I have 3 main categories (CAT, ODB & GPA) Each category has a month (From Jan, to Dec) and each category has a year) Here is a sample of the structure:

CATJAN2002 | CATFEB2002 ... | CATDEC2002 ... | ODBJAN2003 ... | GPAMAR2013

CATJAN2002 | CATFEB2002...| CATDEC2002...| ODBJAN2003...| GPAMAR2013

在vba中，我可以创建一个简单的循环，以将该数据导出到另一个表中:

In vba I can create a simple loop, to export this data into another table:

New_Table字段: ID, TYPE, YEAR, MONTH, VALUE .您能帮我找到一种自动思考表中的列并将数据输入到新表中的方法吗?可以单独在MySQL中完成吗?

New_Table fields: ID, TYPE, YEAR, MONTH, VALUE . Can YOU PLEASE help me find a way to automatically go thought the table's columns and start inputing the data into the new table? Can this be done in MySQL alone?

我忘了第一列(STORE，ITEM和CUSTOMER)的组合是ID(或主键)[SELECT CONCAT(STORE, ITEM, CUSTOMER) AS ID

I forgot to menton that the combination of the first columns (STORE, ITEM & CUSTOMER ) is the ID (Or primary key) [SELECT CONCAT(STORE, ITEM, CUSTOMER) AS ID

推荐答案

想法是使用这样的查询:

The idea is to use a query like this:

SELECT CONCAT(STORE, ITEM, CUSTOMER) ID,
       'CAT' TYPE, 2002 YEAR, 'JAN' MONTH, CATJAN2002 VALUE
FROM yourtable
UNION ALL
SELECT CONCAT(STORE, ITEM, CUSTOMER) ID,
       'CAT' TYPE, 2002 YEAR, 'FEB' MONTH, CATFEB2002 VALUE
FROM yourtable
UNION ALL
...

您可以使用动态查询来做到这一点，例如:

And you can make it using a dynamic query, like this:

SELECT
  GROUP_CONCAT(
  CONCAT(
    'SELECT CONCAT(STORE, ITEM, CUSTOMER) ID,',
    '\'',
    LEFT(column_name, 3),
    '\' TYPE,',
    RIGHT(column_name, 4),
    ' YEAR, \'',
    SUBSTR(column_name, 4, 3),
    '\' MONTH,',
    column_name,
    ' VALUE FROM yourtable')
  SEPARATOR ' UNION ALL ')
FROM information_schema.COLUMNS
where
  table_name = 'yourtable'
  AND (column_name LIKE 'CAT%' OR column_name LIKE 'ODB%' OR column_name LIKE 'GPA%')
INTO @sql;

prepare stm from @sql;
execute stm;

请在此处看到小提琴.

这篇关于MySQL:将列转换为行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！