主流數據庫（SQL Server、Mysql、Oracle）通過sql實現多行數據合爲一行

在主流數據庫如SQL Server、MySQL及Oracle中，處理多行數據的查詢時，有時需要將來自不同行的數據合併到同一行上。這個過程稱為「行轉列」(Pivot)，它允許我們將資料庫的查詢結果從水平方向轉換為垂直方向。以下是如何使用SQL來實現這種功能的詳細說明：

1. SQL PIVOT 基本概念與範例

SQL Server

在SQL Server中，你可以透過`PIVOT`函數來達成這項任務。首先，定義一個基礎表結構，然後用`PIVOT`語句來轉換資料。以下是使用`PIVOT`的一個簡單例子：

-- 假設有一張包含銷售紀錄的表格 'Sales', 如下所示:
CREATE TABLE Sales (Product VARCHAR(20), Store VARCHAR(20), Quantity INT);
INSERT INTO Sales VALUES ('Apple', 'StoreA', 5); -- 插入一筆資料
INSERT INTO Sales VALUES ('Banana', 'StoreB', 3); -- 再插入另一筆資料

-- 接下來使用 `PIVOT` 來取得每家店鋪各自的水果銷量
SELECT * FROM
(
SELECT Product, Store, Quantity
FROM Sales
) AS Source
PIVOT
(
SUM(Quantity) FOR Store IN ([StoreA], [StoreB])
) AS PivotTable;

在上面的例子中，`Source`子查詢提供了原始的資料集，而`PIVOT`部分則定義瞭如何將這些資料轉換。`FOR Store IN`子句指定了要對哪些值進行分組並將其作為新欄位名稱。最後的查詢會返回兩個新的欄位`[StoreA]`和`[StoreB]`，每個欄位包含了相應商店水果的總銷量。

MySQL

雖然MySQL不直接支援`PIVOT`函數，但可以透過自訂函數或結合連結來達到相同的效果。這裡提供了一種方法，但它可能不是最有效率的方式：

-- 在MySQL中，我們可以使用一個自訂函數來模擬 `PIVOT` 的功能
DROP FUNCTION IF EXISTS pivot_data;
DELIMITER //
CREATE FUNCTION pivot_data(source_field VARCHAR(64), pivot_fields TEXT, pivot_values TEXT) RETURNS TEXT DETERMINISTIC NO SIDE EFFECTS BEGIN DECLARE output TEXT DEFAULT ''; DECLARE cols TEXT; DECLARE i INT DEFAULT 1; WHILE i <= CHAR_LENGTH(pivot_values) DO SET cols = CONCAT('`', SUBSTRING(pivot_values, i, 1), '`'); SET output = CONCAT(output, 'MAX(`', source_field || '_' || pivot_fields, '`)', ' AS ''', SUBSTRING(pivot_values, i, 1), '''', ',\n'); SET i = i + 1; END WHILE; SET output = LEFT(output, LENGTH(output) - 1); RETURN CONCAT('SELECT ', cols, ' FROM (', 'SELECT ', source_field || '_', pivot_fields, ' as `', source_field, '`, ', pivot_fields, ' ', 'FROM test_table'), output, ' GROUP BY ', cols); DELIMITER ;

-- 現在可以使用這個自訂函數來執行 pivot 操作:
CALL pivot_data('product', 'a,b,c', 'apple,banana,cherry');

在這個例子中，`pivot_data()`是一個自訂函數，它在背後使用了子查詢來模擬`PIVOT`的功能。注意，由於MySQL不支援`Lateral View`，所以這個解決方案比較複雜且效率較低。

Oracle

在Oracle中，你可以使用`UNION ALL`來拼接來自不同行的資料，並且利用集合運算符來達到類似`PIVOT`的效果：

-- 以下是在Oracle中的示例:
WITH cte AS (
SELECT product, store, quantity FROM sales
UNION ALL
SELECT product, 'Total', sum(quantity) FROM sales
GROUP BY product
)
SELECT product, listagg(store, ', ') within group (order by null) stores, listagg(NVL(store, 'null') || ': ' || NVL(to_char(quantity), 'null'), ', ') within group (order by null) quantities
FROM cte
GROUP BY product;

在上述的CTE（Common Table Expression）中，`UNION ALL`被用於將單個產品的多個店舖資訊拼接起來，然後使用`listagg()`函數以逗號分隔的形式顯示出各店的銷量和總銷量的列表。

2. 實務應用

上述的技巧不僅可以用於簡單的銷售數據報告，還可以在各種場景下發揮作用，例如：

• 商業智慧分析：將不同的商品銷售資料整合在一條記錄內，方便進行深入的分析。

• 監控系統：將不同設備的狀態信息匯總在一條記錄中，以便查看整體的運行情況。

• 網頁應用：當前端需要一次性獲取大量關聯資料時，使用上述技術可以減少資料庫訪問次數。

總之，能夠靈活地處理和展示多行資料是非常重要的能力，尤其是在大規模資料分析和商業決策支持領域。熟悉這些技巧對於職業程式員來說是一項關鍵的技能。