Guruji Point - Code You Want To Write

  • Home
  • Asp.Net
  • WCF
  • SQL
  • Type Script
  • AngularJS
  • Tech News
  • Blog
  • Earn Online

Friday, 16 June 2017

Convert Multiple Rows Into One Row With Comma As Separator In SQL Server

 SQL     No comments   


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. 



  • Share This:  
  •  Facebook
  •  Twitter
  •  Google+
  •  Stumble
  •  Digg
Email ThisBlogThis!Share to XShare to Facebook
Newer Post Older Post Home

0 comments:

Post a Comment

Facebook Updates

Guruji Point

Categories

Comman table Expression in SQL Dynamic Grid Row IQueryable in c# Learn AngularJS Scope_Identity() Use of indexes comma seperated string in sql row_number() and dense_rank() salary table structure. while loop in sql why do we need cursor why tuple used

About Us

This blog is about the Programming ,Tech News and some intresting facts related Contents. Here you can find fundamental and expert level topic of programming languages very practically related to C# , Asp.Net ,Sql-Server, JavaScript, Jquery, WebServices And also Web-Api, WCF ,WPF, Angular Js.

Contact Us

Email Us - gurujipoints@gmail.com
Contact- 8077657477

Reach Us

Copyright © Guruji Point - Code You Want To Write