透视和连接SQL Server中的列中的值 [英] Pivot and concatenate values from column in SQL Server
本文介绍了透视和连接SQL Server中的列中的值的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我的表格具有以下列:
ID | Name | Value
------------------
1 | Test1 | 0
2 | Test2 | 1
3 | Test3 | 0
4 | Test4 | 0
5 | Test5 | 1
而且我想将值列进行透视和连接
And I want to have pivoted and concatenated value column as string
01001
推荐答案
下面的代码将给出预期的结果:
The below code will give the expected result:
SELECT @Result = @Result + CAST(VALUE AS VARCHAR)
FROM #TmpTestingTable
或者您可以使用STUFF
:
SELECT STUFF(
( SELECT CAST(VALUE AS VARCHAR)
FROM #TmpTestingTable
FOR XML PATH ('')
), 1, 0, '')
例如,我将列插入临时表并执行代码.
For sample, I inserted the columns into the temporary table and execute the code.
CREATE TABLE #TmpTestingTable (ID INT, Name VARCHAR (20), Value INT)
INSERT INTO #TmpTestingTable (ID, Name, Value) VALUES
(1 , 'Test1' , 0),
(2 , 'Test2' , 1),
(3 , 'Test3' , 0),
(4 , 'Test4' , 0),
(5 , 'Test5' , 1)
DECLARE @Result AS VARCHAR (100) = '';
-- using variable approach
SELECT @Result = @Result + CAST(VALUE AS VARCHAR)
FROM #TmpTestingTable
SELECT @Result
-- using STUFF approach
SELECT STUFF(
( SELECT CAST(VALUE AS VARCHAR)
FROM #TmpTestingTable
FOR XML PATH ('')
), 1, 0, '')
DROP TABLE #TmpTestingTable
这篇关于透视和连接SQL Server中的列中的值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文