swirl
Home Software Blog Wallpapers Webtools
TSQL - rows to comma separated string
Sunday 01, February 2015   |   Post link
We often need to retrieve a column value from a table into a comma separated string. For example, lets say we have records like:

RollName
1Neel
2Sid
3Meena

We now want a list of names like Neel, Sid, Meena. This is really simple with a little bit of recursion. Here is the code:
-- Lets make a table and fill it with sample data
declare @Student table
(
	roll int primary key identity (1,1),
	name varchar(50) not null
);

insert into @Student (name) values('Neel'), ('Sid'), ('Mina');

-- The main logic
declare @names varchar(max) = '';
select @names = @names + case when @names = '' then name else ', ' + name end from @Student;
select @names;
It's possible to do the same thing using the COALESCE function.
-- Lets make a table and fill it with sample data
declare @Student table
(
	roll int primary key identity (1,1),
	name varchar(50) not null
);

insert into @Student (name) values('Neel'), ('Sid'), ('Mina');

-- The main logic
declare @names varchar(max);
select @names = coalesce(@names + ', ', '') + name from @Student
select @names
The output is Neel, Sid, Mina.
Note, when using coalesce, the starting value of @names is NULL.


Categories: TSQL (5)
Tags: TSQL(3)

Comments

Posts By Year

2023 (5)
2022 (10)
2021 (5)
2020 (12)
2019 (6)
2018 (8)
2017 (11)
2016 (6)
2015 (17)
2014 (2)
2013 (4)
2012 (2)

Posts By Category

.NET (4)
.NET Core (2)
ASP.NET MVC (4)
AWS (5)
AWS API Gateway (1)
Android (1)
Apache Camel (1)
Architecture (1)
Audio (1)
Azure (2)
Book review (3)
Business (1)
C# (3)
C++ (2)
CloudHSM (1)
Containers (4)
Corporate culture (1)
Database (3)
Database migration (1)
Desktop (1)
Docker (1)
DotNet (3)
DotNet Core (2)
ElasticSearch (1)
Entity Framework (3)
Git (3)
IIS (1)
JDBC (1)
Java (9)
Kibana (1)
Kubernetes (1)
Lambda (1)
Learning (1)
Life (7)
Linux (1)
Lucene (1)
Multi-threading (1)
Music (1)
OData (1)
Office (1)
PHP (1)
Photography (1)
PowerShell (2)
Programming (28)
Rants (5)
SQL (2)
SQL Server (1)
Security (2)
Software Engineering (1)
Software development (2)
Solr (1)
Sql Server (2)
Storage (1)
T-SQL (1)
TDD (1)
TSQL (5)
Tablet (1)
Technology (1)
Test Driven (1)
Unit Testing (1)
Unit Tests (1)
Utilities (3)
VC++ (1)
VMWare (1)
VSCode (1)
Visual Studio (2)
Wallpapers (1)
Web API (2)
Win32 (1)
Windows (9)
XML (2)

Posts By Tags

.NET(6) API Gateway(1) ASP.NET(4) AWS(3) Adults(1) Advertising(1) Android(1) Anti-forgery(1) Asynch(1) Authentication(2) Azure(2) Backup(1) Beliefs(1) BlockingQueue(1) Book review(2) Books(1) Busy(1) C#(4) C++(3) CLR(1) CORS(1) CSRF(1) CTE(1) Callbacks(1) Camel(1) Certificates(1) Checkbox(1) CloudHSM(1) Cmdlet(1) Company culture(1) Complexity(1) Consumer(1) Consumerism(1) Containers(3) Core(2) Custom(2) DPI(1) Data-time(1) Database(4) Debugging(1) Delegates(1) Developer(2) Dockers(2) DotNetCore(3) EF 1.0(1) Earphones(1) Elastic Search(1) ElasticSearch(1) Encrypted(1) Entity framework(1) Events(1) File copy(1) File history(1) Font(1) Git(2) HierarchyID(1) IIS(1) Installing(1) Intelli J(1) JDBC(1) JSON(1) JUnit(1) JWT(1) Java(3) JavaScript(1) Kubernetes(1) Life(1) LinkedIn(1) Linux(2) Localization(1) Log4J(1) Log4J2(1) Lucene(1) MVC(4) Management(2) Migration history(1) Mirror(1) Mobile Apps(1) Modern Life(1) Money(1) Music(1) NGINX(1) NTFS(1) NUnit(2) OData(1) OPENXML(1) Objects(1) Office(1) OpenCover(1) Organization(1) PHP(1) Paths(1) PowerShell(2) Producer(1) Programming(2) Python(2) QAAC(1) Quality(1) REDIS(2) REST(1) Runtimes(1) S3-Select(1) SD card(1) SLF4J(1) SQL(2) SQL Code-first Migration(1) SSH(2) Sattelite assemblies(1) School(1) Secrets Manager(1) Self reliance(1) Service(1) Shell(1) Solr(1) Sony VAIO(1) Spirituality(1) Spring(1) Sql Express(1) System Image(1) TDD(1) TSQL(3) Table variables(1) Tables(1) Tablet(1) Ubuntu(1) Url rewrite(1) VMWare(1) VSCode(1) Validation(2) VeraCode(1) Wallpaper(1) Wallpapers(1) Web Development(4) Windows(2) Windows 10(2) Windows 2016(2) Windows 8.1(1) Work culture(1) XML(1) Yii(1) iTunes(1) renew(1) security(1)