Introduction
In this article we will learn How to convert multiple rows into a single row with comma separated value? Convert multiple row into single row data in Sql with separator?Convert many rows into a single text string.
Previous Updates
In previous articles we have learnt what is TypeScript and How to Install this on VS2015 .AngularJS basics,. Best basic C# interview questions. What is Cursor in sql and use of cursor.
Description
In many situation you need to convert or display or reverse your multiple rows data into a single row string . It is very easy using Stuff and XML path in SQL server.
Here i am sharing my table structure with Data :-
CREATE TABLE
MytemData( ID INT IDENTITY(1,1), Code VARCHAR(50))
Insert into
MyTempData values ('AC')
Insert into
MyTempData values ('95')
Insert into
MyTempData values ('79')
Insert into
MyTempData values ('BL')
Insert into
MyTempData values ('CDC')
Insert into
MyTempData values ('IIA')
Insert into
MyTempData values ('MNC')
|
After doing this your data should look like this
Now we need to Convert or Transform all the Rows of Code into a Single comma separated Code .
SELECT STUFF((SELECT ',' + CAST(Code AS VARCHAR(10)) [text()]
FROM
MyTempData
WHERE
Code = Code
FOR XML PATH(''), TYPE)
.value('.','VARCHAR(MAX)'),1,1,' ') Code
|
You can use your own separator by replacing comma in the above solution.
