Skip to content
Fix Code Error

How to concatenate text from multiple rows into a single text string in SQL server?

March 13, 2021 by Code Error
Posted By: John

Consider a database table holding names, with three rows:

Peter
Paul
Mary

Is there an easy way to turn this into a single string of Peter, Paul, Mary?

Solution

If you are on SQL Server 2017 or Azure, see Mathieu Renda answer.

I had a similar issue when I was trying to join two tables with one-to-many relationships. In SQL 2005 I found that XML PATH method can handle the concatenation of the rows very easily.

If there is a table called STUDENTS

SubjectID       StudentName
----------      -------------
1               Mary
1               John
1               Sam
2               Alaina
2               Edward

Result I expected was:

SubjectID       StudentName
----------      -------------
1               Mary, John, Sam
2               Alaina, Edward

I used the following T-SQL:

SELECT Main.SubjectID,
       LEFT(Main.Students,Len(Main.Students)-1) As "Students"
FROM
    (
        SELECT DISTINCT ST2.SubjectID, 
            (
                SELECT ST1.StudentName + ',' AS [text()]
                FROM dbo.Students ST1
                WHERE ST1.SubjectID = ST2.SubjectID
                ORDER BY ST1.SubjectID
                FOR XML PATH ('')
            ) [Students]
        FROM dbo.Students ST2
    ) [Main]

You can do the same thing in a more compact way if you can concat the commas at the beginning and use substring to skip the first one so you don’t need to do a sub-query:

SELECT DISTINCT ST2.SubjectID, 
    SUBSTRING(
        (
            SELECT ','+ST1.StudentName  AS [text()]
            FROM dbo.Students ST1
            WHERE ST1.SubjectID = ST2.SubjectID
            ORDER BY ST1.SubjectID
            FOR XML PATH ('')
        ), 2, 1000) [Students]
FROM dbo.Students ST2
Answered By: Ritesh

Related Articles

  • SQL Query for Student mark functionality
  • Using Event Aggregator to load a view with different…
  • How to generate JAXB classes from XSD?
  • SQL query return data from multiple tables
  • Creating a range of dates and specific time in Python
  • SQL JOIN and different types of JOINs
  • Injecting $scope into an angular service function()
  • How to prevent scrolling the whole page?
  • Can a "User Assigned Managed Identity" be used locally?
  • MongoDB: How to find out if an array field contains…
  • Make datetime derived from one column
  • How to create a column with a count of rows between…
  • SQLGrammarException:error executing work ORA-01722:…
  • Dodged bar plot in R based on to columns with count…
  • Multiple elements per v-for loop
  • MS Access SQL query - Count records until value is met
  • Error: undefined Unable to resolve module
  • Printing out all the objects in array list
  • data.table vs dplyr: can one do something well the…
  • Database development mistakes made by application developers
  • Using StringWriter for XML Serialization
  • Converting complex table with groupings in r to dataframe
  • How do I view model data after fetch in backbone.js
  • How to use azure-sb in aurelia
  • Proper way to sideload data with ember-model
  • INNER JOIN vs LEFT JOIN performance in SQL Server
  • org.hibernate.MappingException: Could not determine…
  • To refactor multiple || and && conditions…
  • Simple way to transpose columns and rows in SQL?
  • Insert/Update Many to Many Entity Framework . How do…
  • Getting the highest value of an JSON array and…
  • What is the right way to populate a DropDownList…
  • Best way to extract messy HTML tables using BeautifulSoup
  • Confused with basic java stuffs
  • Using IS NULL or IS NOT NULL on join conditions -…
  • Difference between FetchType LAZY and EAGER in Java…
  • Vuejs - Assign computed properties to data()
  • how to get the df names and first input value for…
  • Exception in thread "main"…
  • Timer trigger Azure Function stopped working without…
  • Filter a dataframe meeting some criteria
  • How to select something from JOIN? SQL
  • How to Update Database from Assets Folder in App
  • node.js TypeError: path must be absolute or specify…
  • Three.js: Cannot display mesh created with texture array
  • Powershell New-ADUser issue with -AccountPassword
  • SQL query to get number of clients with last…
  • Query on usage of on-premises data gateway for…
  • T-SQL stored procedure that accepts multiple Id values
  • multilevel employee manager relationship through…
  • Use SQL Server Management Studio to connect remotely…
  • Identifying and solving…
  • Aurelia Child Router redirect to specific route
  • How do i set VM generation while creating disk using…
  • Use Azure Key Vault to get secrets
  • Logging best practices
  • Ember.Checkbox not working as expected after outer…
  • I need to extract working hour breaks out of a Time…
  • Update the Azure DevOps service endpoint…
  • Python DataFrame: Map two dataframes based on day of month?
  • Is there a way to pickup backlog item data from the…
  • why can't I select inner level index elements in a…
  • SQL left join vs multiple tables on FROM line?
  • Incorrect SAS URL produced for blob when using…
  • Pandas Merging 101
  • How to query for Xml values and attributes from…
  • Passing data from controller to view in Laravel
  • AppCompat v7 r21 returning error in values.xml?
  • For Loop on Lua
  • What's the difference between a temp table and table…
  • How do you parse and process HTML/XML in PHP?
  • Detect if Visual C++ Redistributable for Visual…
  • Aurelia, navigate is not updating viewport
  • Webpack build error
  • How to use Selenium in Databricks and accessing and…
  • Ubuntu apt-get unable to fetch packages
  • Creating Azure VM by using VM admin password from…
  • What does an exclamation mark mean in the Swift language?
  • SQL find sum of entries by date including previous date
  • Ember-data lazy load association with "links" attribute
  • SQL Inner-join with 3 tables?
  • How do I obtain a Query Execution Plan in SQL Server?
  • Why cat does not work with parameter -0 in xargs?
  • How do I include certain conditions in SQL Count
  • T-SQL How to create tables dynamically in stored procedures?
  • Azure: Web Apps - List Application Settings from…
  • java.sql.SQLException: - ORA-01000: maximum open…
  • Convert python script to airflow dag
  • How can I append a query parameter to an existing URL?
  • Asp.net core WebApi endpoint for many to many tables
  • Child routes in aurelia, additional configureRoutes…
  • Html ordered list 1.1, 1.2 (Nested counters and…
  • I want to create a SQLite database like in the…
  • How to return rows from left table not found in right table?
  • Azure Function long duration and App Service Plan,…
  • how to show progress bar(circle) in an activity…
  • How to launch a new VM with azure VM image using…
  • How do you clear the SQL Server transaction log?
  • Union of multiple Database queries with same parameters
  • String.equals() with multiple conditions (and one…

Disclaimer: This content is shared under creative common license cc-by-sa 3.0. It is generated from StackExchange Website Network.

Post navigation

Previous Post:

Set select option ‘selected’, by value

Next Post:

PUT vs. POST in REST

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

.net ajax android angular arrays aurelia backbone.js bash c++ css dataframe ember-data ember.js excel git html ios java javascript jquery json laravel linux list mysql next.js node.js pandas php polymer polymer-1.0 python python-3.x r reactjs regex sql sql-server string svelte typescript vue-component vue.js vuejs2 vuetify.js

  • you shouldn’t need to use z-index
  • No column in target database, but getting “The schema update is terminating because data loss might occur”
  • Angular – expected call-signature: ‘changePassword’ to have a typedeftslint(typedef)
  • trying to implement NativeAdFactory imports deprecated method by default in flutter java project
  • What should I use to get an attribute out of my foreign table in Laravel?
© 2022 Fix Code Error