I have table with these columns:
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
3 Answers
The below code will give the expected result:
SELECT @Result = @Result + CAST(VALUE AS VARCHAR)
FROM #TmpTestingTable
Or you can use the 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
1 Comment
t-clausen.dk
calling concatination the STUFF approach is really misleading. All your STUFF does is replacing the first character with empty string
Use FOR XML to concatinate. It is important that you also include an ORDER BY. Otherwise you have no control of the order of the values and you risk an arbitrary order.
SELECT
(SELECT CAST([VALUE] AS CHAR(1))
FROM yourtable
ORDER BY ID
FOR XML PATH ('')
)
1 Comment
tomipnh
Clean, simple, love it :)
SELECT GROUP_CONCAT(Value SEPARATOR '') FROM Table
EDIT:
Not working on SQL Server. Have a look at Simulating group_concat MySQL function in Microsoft SQL Server 2005? to try to make it work
2 Comments
HoneyBadger
Looks like mysql, not sql-server
tomipnh
I found groupconcat.codeplex.com but I don't want to use external software
DECLARE @Txt1 VARCHAR(MAX) SET @Txt1='' SELECT @Txt1 = @Txt1 + CONVERT(VARCHAR, Value) FROM your_table SELECT @Txt1