在MySQL数据库中，行转列的方法如下示例：
1.使用CASE或IF语句
CREATE TABLE sales_data (
product_id INT,
year INT,
sales INT
);
INSERT INTO sales_data (product_id, year, sales)
VALUES (1, 2018, 100),
(1, 2019, 200),
(1, 2020, 300),
(2, 2018, 150),
(2, 2019, 250),
(2, 2020, 350);
SELECT product_id,
SUM(CASE WHEN year = 2018 THEN sales ELSE 0 END) AS '2018',
SUM(CASE WHEN year = 2019 THEN sales ELSE 0 END) AS '2019',
SUM(CASE WHEN year = 2020 THEN sales ELSE 0 END) AS '2020'
FROM sales_data
GROUP BY product_id;
或
SELECT product_id,
SUM(IF(year = 2018, sales, 0)) AS '2018',
SUM(IF(year = 2019, sales, 0)) AS '2019',
SUM(IF(year = 2020, sales, 0)) AS '2020'
FROM sales_data
GROUP BY product_id;
2.使用GROUP_CONCAT函数
CREATE TABLE employee_skills (
employee_id INT,
skill VARCHAR(255)
);
INSERT INTO employee_skills (employee_id, skill)
VALUES (1, 'Java'),
(1, 'Python'),
(2, 'Java'),
(2, 'C++'),
(3, 'Python');
SELECT employee_id,
GROUP_CONCAT(skill) AS skills
FROM employee_skills
GROUP BY employee_id;
3.使用UNION ALL和子查询
CREATE TABLE sales_data (
product_id INT,
year INT,
sales INT
);
INSERT INTO sales_data (product_id, year, sales)
VALUES (1, 2018, 100),
(1, 2019, 200),
(1, 2020, 300),
(2, 2018, 150),
(2, 2019, 250),
(2, 2020, 350);
SELECT product_id, '2018' AS year, sales AS sales_2018
FROM sales_data
WHERE year = 2018
UNION ALL
SELECT product_id, '2019' AS year, sales AS sales_2019
FROM sales_data
WHERE year = 2019
UNION ALL
SELECT product_id, '2020' AS year, sales AS sales_2020
FROM sales_data
WHERE year = 2020;
4.使用PIVOT（似适用手MySQL 5.7及更高版本）
CREATE TABLE sales_data (
product_id INT,
year INT,
sales INT
);
INSERT INTO sales_data (product_id, year, sales)
VALUES (1, 2018, 100),
(1, 2019, 200),
(1, 2020, 300),
(2, 2018, 150),
(2, 2019, 250),
(2, 2020, 350);
SELECT * FROM sales_data
PIVOT(SUM(sales) FOR year IN (2018, 2019, 2020));
5.编写存储过程（不推荐）
DELIMITER
CREATE PROCEDURE DynamicPivot()
BEGIN
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'SUM(IF(year = ''',
year,
''', sales, 0)) AS ',
'"',
year,
'"'
)
) INTO @sql
FROM sales_data;
SET @sql = CONCAT('SELECT product_id, ', @sql, ' FROM sales_data GROUP BY product_id');
PREPARE stmt FROM @sql;
EXECUTE stmt;
END//
DELIMITER ;
CALL DynamicPivot();
