[SQL Server]一列多行转换为字符串
在 SQL Server 中,如何将多行数据变成一个字符串保存。
skill |
---|
投石 |
挖矿 |
刮痧 |
上面三行数据想要得到结果为:投石,挖矿,刮痧 |
有两种方式:
- 拼接字符串。
- 使用
for XML
首先创建模拟用的数据
IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL
BEGIN
DROP TABLE #TempTable;
END
CREATE TABLE #TempTable
(
[name] VARCHAR(MAX),
[skill] VARCHAR(MAX)
)
INSERT INTO #TempTable VALUES('钟离','投石')
INSERT INTO #TempTable VALUES('钟离','挖矿')
INSERT INTO #TempTable VALUES('钟离','刮痧')
INSERT INTO #TempTable VALUES('魈','花臂男')
INSERT INTO #TempTable VALUES('魈','打桩机')
INSERT INTO #TempTable VALUES('魈','矮个子')
INSERT INTO #TempTable VALUES('可莉','超可爱')
INSERT INTO #TempTable VALUES('可莉','超萌')
INSERT INTO #TempTable VALUES('可莉','蹦蹦炸弹')
--SELECT * FROM #TempTable
name skill
------- -----------
钟离 投石
钟离 挖矿
钟离 刮痧
魈 花臂男
魈 打桩机
魈 矮个子
可莉 超可爱
可莉 超萌
可莉 蹦蹦炸弹
1. 使用拼接方式
DECLARE @skills varchar(MAX)
SET @skills = '' -- 用这种方式必须要初始化变量
SELECT @skills = @skills + ',' + [skill] FROM #TempTable WHERE [name] = '钟离'
SELECT @skills
(No column name)
--------------------
,投石,挖矿,刮痧
2. 使用 for XML
SELECT ',' + [skill]
FROM #TempTable
WHERE [name] = '钟离'
FOR XML PATH('')
XML_F52E2B61-18A1-11d1-B105-00805F49916B
-------------------------------------------------------
,投石,挖矿,刮痧
去掉开头的分隔符
可以使用 left
, right
, stuff
函数去掉分隔符
Stuff (@expression,@startIndex,@length,@replacrExpression)
- @expression 表示 要处理的字符串
- @startIndex 表示 开始删除字符串的位置(sql server索引从1而不是0开始)
- @lenght 表示 删除字符串的长度
- @replacrExpression 表示 删除后该位置 要替换的字符串
SELECT STUFF(@skills, 1, 1, '')
分组
分组需要嵌套
SELECT
[name]
, STUFF(
(SELECT ',' + [skill]
FROM #TempTable A
WHERE A.[name] = B.[name]
FOR XML PATH(''))
,1
,1
,''
) AS [skills]
FROM #TempTable B
GROUP BY [name]
name skills
------- -------------------------
可莉 超可爱,超萌,蹦蹦炸弹
魈 花臂男,打桩机,矮个子
钟离 投石,挖矿,刮痧