sql中的行转列函数
可以使用 CASE 语句来实现行转列。
假设有一个表 my_table,其结构如下:
CREATE TABLE my_table (
id INT,
value VARCHAR(50),
column_name VARCHAR(50)
);
INSERT INTO my_table (id, value, column_name) VALUES
(1, 'apple', 'column1'),
(1, 'banana', 'column2'),
(1, 'cherry', 'column3'),
(2, 'date', 'column1'),
(2, 'elderberry', 'column2'),
(2, 'fig', 'column3');
使用 CASE 语句将行转换为列:
SELECT id,
MAX(CASE WHEN column_name = 'column1' THEN value END) AS column1,
MAX(CASE WHEN column_name = 'column2' THEN value END) AS column2,
MAX(CASE WHEN column_name = 'column3' THEN value END) AS column3
FROM my_table
GROUP BY id;
输出结果将是:
id column1 column2 column3
1 apple banana cherry
2 date elderberry fig