大家好，我是全棧小5，歡迎來到《小5講堂》。
這是《Sql Server》系列文章，每篇文章將以博主理解的角度展開講解。
溫馨提示：博主能力有限，理解水平有限，若有不對之處望指正！

前言

很久沒有寫報表了，今天嘗試寫下報表就遇到了一個常見但是太久了有點忘了的知識點。
那就是如何讓指定字段的行數據轉為一個字段顯示并以逗號的形式隔開，一起來探討下！

示例

STRING_AGG 是SQL Server 2017引入的新函數，更簡潔高效

數據集

模擬表格數據，如下

select '張三' as NameText,'語文' as SubjectText
union all
select '張三' as NameText,'數學' as SubjectText
union all
select '張三' as NameText,'英語' as SubjectText
union all
select '張三' as NameText,'物理' as SubjectText
union all
select '張三' as NameText,'化學' as SubjectText
union all
select '張三' as NameText,'生物' as SubjectText
union all
select '張三' as NameText,'歷史' as SubjectText
union all
select '張三' as NameText,'政治' as SubjectText
union all
select '張三' as NameText,'地理' as SubjectText
union all
select '張三' as NameText,'體育' as SubjectText
union all
select '張三' as NameText,'音樂' as SubjectText
union all
select '張三' as NameText,'美術' as SubjectText
union all
select '李四' as NameText,'體育' as SubjectText
union all
select '李四' as NameText,'音樂' as SubjectText
union all
select '李四' as NameText,'美術' as SubjectText

數據分組

以姓名為分組，增加多一個科目字段，并且科目名稱以逗號形式隔開，效果如下：

string_agg(cast(字段 as varchar), ‘,’) as 自定義名稱

select t.NameText
,string_agg(cast(t.SubjectText as varchar), ',') as ALLSubject
from(select '張三' as NameText,'語文' as SubjectTextunion allselect '張三' as NameText,'數學' as SubjectTextunion allselect '張三' as NameText,'英語' as SubjectTextunion allselect '張三' as NameText,'物理' as SubjectTextunion allselect '張三' as NameText,'化學' as SubjectTextunion allselect '張三' as NameText,'生物' as SubjectTextunion allselect '張三' as NameText,'歷史' as SubjectTextunion allselect '張三' as NameText,'政治' as SubjectTextunion allselect '張三' as NameText,'地理' as SubjectTextunion allselect '張三' as NameText,'體育' as SubjectTextunion allselect '張三' as NameText,'音樂' as SubjectTextunion allselect '張三' as NameText,'美術' as SubjectTextunion allselect '李四' as NameText,'體育' as SubjectTextunion allselect '李四' as NameText,'音樂' as SubjectTextunion allselect '李四' as NameText,'美術' as SubjectText
) t
group by t.NameText

增加排序

通過輸出結果可能會注意到，以逗號隔開的文本不是按順序，和表格本身排序不一致。
假設根據id進行升序排序輸出，確保一致，如下：

string_agg(cast(字段 as varchar), ‘,’) within group (order by 排序字段 asc) as 自定義名稱

select t.NameText
,string_agg(cast(t.SubjectText as varchar), ',') within group (order by t.id asc) as ALLSubject
from(select 1 as id,'張三' as NameText,'語文' as SubjectTextunion allselect 2 as id,'張三' as NameText,'數學' as SubjectTextunion allselect 3 as id,'張三' as NameText,'英語' as SubjectTextunion allselect 4 as id,'張三' as NameText,'物理' as SubjectTextunion allselect 5 as id,'張三' as NameText,'化學' as SubjectTextunion allselect 6 as id,'張三' as NameText,'生物' as SubjectTextunion allselect 7 as id,'張三' as NameText,'歷史' as SubjectTextunion allselect 8 as id,'張三' as NameText,'政治' as SubjectTextunion allselect 9 as id,'張三' as NameText,'地理' as SubjectTextunion allselect 10 as id,'張三' as NameText,'體育' as SubjectTextunion allselect 11 as id,'張三' as NameText,'音樂' as SubjectTextunion allselect 12 as id,'張三' as NameText,'美術' as SubjectTextunion allselect 13 as id,'李四' as NameText,'體育' as SubjectTextunion allselect 14 as id,'李四' as NameText,'音樂' as SubjectTextunion allselect 15 as id,'李四' as NameText,'美術' as SubjectText
) t
group by t.NameText

查詢小技巧

以下是三個能提高你SQL Server查詢效率和便利性的實用技巧：

1. 使用 WITH TIES 獲取并列結果（分頁查詢時特別有用）

-- 獲取前10條記錄，包括與第10條記錄值相同的所有記錄
SELECT TOP 10 WITH TIES column1, column2
FROM table_name
ORDER BY column1 DESC;

應用場景：當你想獲取排名靠前的記錄，但不想因為TOP N的限制而遺漏與第N條記錄值相同的其他記錄。

2. 使用 OUTPUT 子句捕獲DML操作結果（避免二次查詢）

-- 更新數據同時返回被更新的記錄
UPDATE table_name
SET column1 = 'new_value'
OUTPUT inserted.*  -- 返回更新后的數據
WHERE condition;-- 刪除數據同時返回被刪除的記錄
DELETE FROM table_name
OUTPUT deleted.*  -- 返回被刪除的數據
WHERE condition;

優勢：減少數據庫往返次數，提高效率，特別適合需要記錄變更的場景。

3. 使用 CROSS APPLY 優化復雜查詢（替代JOIN的更好選擇）

-- 獲取每個客戶的最新訂單
SELECT c.CustomerID, c.CustomerName, o.OrderDate, o.Amount
FROM Customers c
CROSS APPLY (SELECT TOP 1 OrderDate, AmountFROM OrdersWHERE CustomerID = c.CustomerIDORDER BY OrderDate DESC
) o;

優勢：

• 比子查詢更高效
• 比LEFT JOIN + GROUP BY更簡潔
• 特別適合需要為每行主表獲取一個相關子表記錄的場景

這些技巧能幫助你寫出更高效、更簡潔的SQL查詢語句，提高數據庫操作效率。

文章推薦

【數據庫】使用Sql Server將分組后指定字段的行數據轉為一個字段顯示，并且以逗號隔開每個值，收藏不迷路

【數據庫】SQL Server 查詢條件小技巧：ISNULL 函數的使用，有請DeepSeek來輔助講解下

【Sql Server】在SQL Server中生成雪花ID(Snowflake ID)

【Sql Server】使用row_number over方式進行表分頁，數據量達到五千多條記錄后，查詢變慢需要20多秒的解決方案

【Sql Server】隨機查詢一條表記錄，并重重溫回顧下自定義函數的封裝和使用

【Sql Server】鎖表如何解鎖，模擬會話事務方式鎖定一個表然后進行解鎖

【Sql Server】通過Sql語句批量處理數據，使用變量且遍歷數據進行邏輯處理

【新星計劃回顧】第六篇學習計劃-通過自定義函數和存儲過程模擬MD5數據

【新星計劃回顧】第四篇學習計劃-自定義函數、存儲過程、隨機值知識點

【Sql Server】Update中的From語句，以及常見更新操作方式

【Sql server】假設有三個字段a,b,c 以a和b分組，如何查詢a和b唯一，但是c不同的記錄

【Sql Server】新手一分鐘看懂在已有表基礎上修改字段默認值和數據類型

總結：溫故而知新，不同階段重溫知識點，會有不一樣的認識和理解，博主將鞏固一遍知識點，并以實踐方式和大家分享，若能有所幫助和收獲，這將是博主最大的創作動力和榮幸。也期待認識更多優秀新老博主。