CODEWITHSUNDEEP
sql-serverpivot
tomipnh
tomipnh

Reputation: 193

Pivot and concatenate values from column in SQL Server

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

Upvotes: 3

Views: 2755

Answers (3)

t-clausen.dk
t-clausen.dk

Reputation: 44316

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 ('')
  )

Upvotes: 3

Arulkumar
Arulkumar

Reputation: 13237

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

Upvotes: 3

Theo Babilon
Theo Babilon

Reputation: 661

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

Upvotes: 0

Related Questions