SQL Server -联接多个表，使多行成为一行

SELECT Product,
       [2020], [2021]
FROM (
    SELECT Product, Year, Sales
    FROM Sales
) AS SourceTable
PIVOT (
    SUM(Sales)
    FOR Year IN ([2020], [2021])
) AS PivotTable;

SELECT Product,
       SUM(CASE WHEN Year = 2020 THEN Sales ELSE 0 END) AS [2020],
       SUM(CASE WHEN Year = 2021 THEN Sales ELSE 0 END) AS [2021]
FROM Sales
GROUP BY Product;

DECLARE @cols AS NVARCHAR(MAX),
        @query AS NVARCHAR(MAX);

SELECT @cols = COALESCE(@cols + ', ', '') + QUOTENAME(Year)
FROM (SELECT DISTINCT Year FROM Sales) AS Years;

SET @query = 'SELECT Product, ' + @cols + '
             FROM (
                 SELECT Product, Year, Sales
                 FROM Sales
             ) AS SourceTable
             PIVOT (
                 SUM(Sales)
                 FOR Year IN (' + @cols + ')
             ) AS PivotTable;';

EXEC sp_executesql @query;