[Feature Request]: pivot

[fengttt]

Is there an existing issue for the same feature request?

• [X] I have checked the existing issues.

Is your feature request related to a problem?

No response

Describe the feature you'd like

PIVOT, as described in

https://learn.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-ver16

Describe implementation you've considered

No response

Documentation, Adoption, Use Case, Migration Strategy

No response

Additional information

No response

[fengttt]

There is a related UNPIVOT function in MS SQLServer.

Note that like #10060, UNPIVOT has cross apply/lateral semantics by default.

[dengn]

Require specification about the implemented syntax and using details for doc side.

[domingozhang]

SELECT non-pivoted column,  
    {first pivoted column} AS column name,  
    [
    {second pivoted column} AS column name,  
    ...  
     last pivoted column] AS column name 
     ]
FROM  
    ({SELECT query that produces the data})   
    [AS] {alias}
{PIVOT  | UNPIVOT}
(  
    {aggregation function}({aggregated column})  
FOR   
[{column that contains the values that will become column headers}]   
    IN ( [first pivoted column], [second pivoted column],  
    ... [last pivoted column])  
) [AS] {alias}
[ORDER BY clause];

[domingozhang]

Examples： CREATE TABLE students( stu_id int primary key auto_increment, stu_name varchar(100) NULL, stu_hobby varchar(100) NULL, stu_hours int NULL ) ;

INSERT INTO students(stu_name,stu_hobby,stu_hours) VALUES ('userA','sing','2') ,('userA','dance','5') ,('userB','sing','5') ,('userB','dance','3') ,('userC','sing','1') ,('userC','dance','5') ,('userD','swim','5') ,('userD','dance','5');

Pivot Query:

SELECT * FROM ( SELECT stu_name,stu_hobby,stu_hours FROM students )t PIVOT(SUM(stu_hours) FOR stu_hobby IN ('唱歌','跳舞','游泳')) as piv;

Unpivot Query: SELECT new_column,T,stu_hours FROM students UNPIVOT ( T FOR new_column IN(stu_hobby,stu_name) )un;

[domingozhang]

CREATE TABLE pvt (VendorID INT, Emp1 INT, Emp2 INT,
Emp3 INT, Emp4 INT, Emp5 INT);
INSERT INTO pvt VALUES (1,4,3,5,4,4);
INSERT INTO pvt VALUES (2,4,1,5,5,5);
INSERT INTO pvt VALUES (3,4,3,5,4,4);
INSERT INTO pvt VALUES (4,4,2,5,5,4);
INSERT INTO pvt VALUES (5,5,1,5,5,5);

-- Unpivot the table.
SELECT VendorID, Employee, Orders
FROM
(SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
FROM pvt) p
UNPIVOT
(Orders FOR Employee IN
(Emp1, Emp2, Emp3, Emp4, Emp5)
)AS unpvt;

[domingozhang]

CREATE TABLE StudentScores ( UserName VARCHAR(20),
Subject VARCHAR(30),
Score decimal(5,2)
); Example II INSERT INTO StudentScores values('张三', '语文', 80); INSERT INTO StudentScores values('张三', '数学', 90); INSERT INTO StudentScores values('张三', '英语', 70); INSERT INTO StudentScores values('张三', '生物', 85); INSERT INTO StudentScores values('李四', '语文', 80); INSERT INTO StudentScores values('李四', '数学', 92); INSERT INTO StudentScores values('李四', '英语', 76); INSERT INTO StudentScores values('李四', '生物', 88); INSERT INTO StudentScores values('码农', '语文', 60); INSERT INTO StudentScores values('码农', '数学', 82); INSERT INTO StudentScores values('码农', '英语', 96); INSERT INTO StudentScores values('码农', '生物', 78);

SELECT * FROM StudentScores AS P PIVOT ( SUM(Score) FOR p.Subject IN ('语文','数学','英语','生物') ) AS T;

[dengn]

syntax and examples as SQL Server.