将SQL单行多列输出转换为两列(键、值)和多行输出

CREATE TABLE employee (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    department VARCHAR(100)
);

INSERT INTO employee (id, name, age, department) VALUES
(1, 'Alice', 30, 'HR'),
(2, 'Bob', 25, 'Engineering');

SELECT id, column_name, column_value
FROM (
    SELECT id, name, age, department
    FROM employee
) AS pvt
UNPIVOT (
    column_value FOR column_name IN (name, age, department)
) AS unpvt;

id | column_name | column_value
---|-------------|--------------
1  | name        | Alice
1  | age         | 30
1  | department  | HR
2  | name        | Bob
2  | age         | 25
2  | department  | Engineering

SELECT id,
       'name' AS column_name, name AS column_value
FROM employee
UNION ALL
SELECT id,
       'age' AS column_name, CAST(age AS VARCHAR) AS column_value
FROM employee
UNION ALL
SELECT id,
       'department' AS column_name, department AS column_value
FROM employee;