數(shù)據(jù)庫自帶的行轉列函數(shù)有哪些呢？這里要介紹PIVOT 和 UNPIVOT 函數(shù)了。這兩個函數(shù)為我們提供了便捷的方式來實現(xiàn)數(shù)據(jù)表的行列轉換。PIVOT 用于旋轉數(shù)據(jù)，將行轉為列，UNPIVOT 是其逆操作，將列轉為行。

下面是一些示例：

PIVOT 示例：

SELECT * FROM ( SELECT year, month, qty FROM Sales)PIVOT (SUM(qty) FOR month IN ('Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'))

在這個例子中，我們將“月份”列中的每個月份轉換為了各自的列，并將每個月的銷售量總和填充到相應的列中。

UNPIVOT 示例：

SELECT * FROM ( SELECT year, Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec FROM Sales) UNPIVOT (qty FOR month IN (Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec))

在這個例子中，我們將每個月的列轉換為了一個“月份”列，并將相應的銷售量總和填充到新的“qty”列中。

目前，Oracle和SQL Server數(shù)據(jù)庫系統(tǒng)都支持 PIVOT 和 UNPIVOT 函數(shù)。MySQL和PostgreSQL并不直接支持，但可以通過其他SQL語句實現(xiàn)類似的效果（如上篇文章介紹的方法）。

那如果要轉為列的行，它的內(nèi)容和個數(shù)不確定，該怎么辦？比如上面示例中的月份不是固定的12個月。

我首先想到的是利用SQL的子查詢，比如：

SELECT ...FROM ...PIVOT (SUM(value) FOR column IN (SELECT DISTINCT column FROM ...))

但以上查詢在SQL Server中是無效的，這是因為SQL Server和Oracle在編譯查詢時需要知道所有的列名，而子查詢返回的結果直到運行時才知道。

解決此問題的一種常見方法是使用動態(tài)SQL，即使用SQL編寫并執(zhí)行SQL語句。這樣，你可以先運行一個查詢來獲取所有唯一的列或行名，然后將這些名字拼接到你的PIVOT或UNPIVOT查詢中，最后執(zhí)行這個查詢。

假設我們有一個名為sales的表，其中包含以下數(shù)據(jù)：

Product   | Year | Sale
--------------------------
ProductA  | 2019 | 100
ProductA  | 2020 | 150
ProductB  | 2019 | 200
ProductB  | 2020 | 220
ProductC  | 2019 | 300
ProductC  | 2020 | 350

我們希望按產(chǎn)品進行行列轉換，得到以下結果：

Year | ProductA | ProductB | ProductC
--------------------------------------
2019 | 100      | 200      | 300
2020 | 150      | 220      | 350

如果產(chǎn)品的類別是固定的，我們可以使用靜態(tài)SQL來實現(xiàn)。例如，在SQL Server中，我們可以使用PIVOT操作符：

SELECT Year, [ProductA], [ProductB], [ProductC] FROM (SELECT Product, Year, Sale FROM sales) AS SourceTable PIVOT (SUM(Sale) FOR Product IN ([ProductA], [ProductB], [ProductC])) AS PivotTable;

但是，如果產(chǎn)品的類別是動態(tài)的，我們需要使用動態(tài)SQL。在SQL Server中，我們可以使用以下方法：

DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);SET @columns = STUFF((  SELECT ',' + QUOTENAME(Product)  FROM sales  GROUP BY Product  ORDER BY Product  FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '');
SET @sql = N'SELECT Year, ' + @columns + 'FROM (  SELECT Product, Year, Sale  FROM sales) AS SourceTablePIVOT (  SUM(Sale)  FOR Product IN (' + @columns + ')) AS PivotTable;';
EXEC sp_executesql @sql;

這個例子首先構造了一個包含所有產(chǎn)品的列名的字符串（@columns），然后使用這個字符串來構造PIVOT查詢的SQL語句（@sql），最后執(zhí)行這個SQL語句。