将行转换为TSQL列

Question

如何将以下示例从行转换为列？

例如。

create table test
(
  date_from date,
  first_name varchar(50),
  code int,
  total decimal

)


Insert into test(date_from, first_name,code, total) values (getdate(), 'John', 1, 5);
Insert into test(date_from, first_name,code, total) values (getdate() + 1, 'John', 1, 10);
Insert into test(date_from, first_name,code, total) values (getdate() + 2, 'John', 1, 15);

Insert into test(date_from, first_name,code, total) values (getdate(), 'John', 2, 6);
Insert into test(date_from, first_name,code, total) values (getdate() + 1, 'John', 2, 12);
Insert into test(date_from, first_name,code, total) values (getdate() + 2, 'John', 2, 18);

Insert into test(date_from, first_name,code, total) values (getdate(), 'Oliver', 1, 5);
Insert into test(date_from, first_name,code, total) values (getdate() + 1, 'Oliver', 1, 10);
Insert into test(date_from, first_name,code, total) values (getdate() + 2, 'Oliver', 1, 15);

Insert into test(date_from, first_name,code, total) values (getdate(), 'Oliver', 2, 6);
Insert into test(date_from, first_name,code, total) values (getdate() + 1, 'Oliver', 2, 12);
Insert into test(date_from, first_name,code, total) values (getdate() + 2, 'Oliver', 2, 18);

我需要折射为：

Answer 1

SELECT  [first_name], [code],
        [2022-07-01], [2022-07-02], [2022-07-03]
FROM (
    SELECT *
    FROM dbo.test
) AS SourceTable
PIVOT (
    AVG(total)
    FOR date_from IN ([2022-07-01], [2022-07-02], [2022-07-03])
) AS PivotTable;