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 TwitterShare to Facebook
Newer Post Older Post Home

0 comments:

Post a comment

Facebook Updates

Guruji Point

Popular Posts

  • Base64 Encoding And Decoding In SQL Server
    Introduction In this article we will learn How to encode a string into Base64String and how to decode Base64String to string. How to crea...
  • Trigger In SQL Server - Insert Update Delete In Single Trigger With Example
    Introduction In this article we will learn what is trigger . How to manage Insert, Update and Delete operation in trigger. Insert Update ...
  • Convert DataSet To List Or GenericList Using LINQ - C#
    Introduction In this article we will learn how to convert dataset to list or convert DataSet to List of collection o generic list in c#. ...
  • Difference Between DataAdapter And DataReader In Asp.Net With Example
    Introduction In this article we will learn what is DataAdapter and DataReader. What is the difference between dataadapter and datareader....
  • Read List With In List Collection Using LINQ - Increase Application Data Performances
    Introduction In this article we will learn about how to read data from a SubList or list with in list collection. How to access sub list p...

Categories

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

Best Deal Here

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