Concatenate multiple rows to form one single row in SQL Server?

sql multiple rows into one row with multiple columns
sql combine multiple rows into one row
sql combine multiple rows into one column
multiple sql rows merge into single row if the id is same
how to get multiple row data in single row in sql
sql concatenate rows into string group by
sql left join multiple rows into one
sql combine two rows into one
Overview

I need to build a description field that describes an entity. The data I am working with has the property description split for each individual key in my table. Below is an example of what the data looks like:

+------------+--------------------+----------+
|    Key     |        Desc        | Order_Id |
+------------+--------------------+----------+
| 5962417474 | Big Yellow Door    |    14775 |
| 5962417474 | Orange Windows     |    14776 |
| 5962417474 | Blue Triangle Roof |    14777 |
+------------+--------------------+----------+

Originally, I wrote a query using an aggregate function like so:

SELECT
    [P].[KEY],
    CONCAT (MIN([P].[Desc]), + ' ' + MAX([P].[Desc])) [PROPERTY_DESCRIPTION]
FROM [dbo].[PROP_DESC] [P] 
WHERE [P].[KEY] = '5962417474'
GROUP BY [P].[KEY];

This worked great for two row entries but then I realized what if I have multiple records for a property description? So I wrote the following query to check if I had multiple property descriptions:

SELECT 
    [P].[KEY], COUNT([P].[KEY])
FROM [dbo].[PROP_DESC] [P]
GROUP BY [P].[KEY]
HAVING COUNT(*) > 2; -- Returns one record which is the above table result.

This gave me back a record with three descriptions so my original query will not work. How can I tackle this problem down when there are multiple fields?

Desired Output
+------------+---------------------------------------------------+----------+
|    Key     |                       Desc                        | Order_Id |
+------------+---------------------------------------------------+----------+
| 5962417474 | Big Yellow Door Orange Windows Blue Triangle Roof |    14775 |
+------------+---------------------------------------------------+----------+

It depends on what SQL language you're using, but you'll want to use some kind of group concat / array agg function. Eg:

SELECT
  Key,
  STRING_AGG(desc, ', ')
  FROM TABLE
  GROUP BY Key;

How to concatenate text from multiple rows into a single text string in , 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. How to Rollup Multiple Rows into a Single Row in SQL Server. Rolling up data from multiple rows into a single row may be necessary for concatenating data, reporting, exchanging data between systems and more. This can be accomplished by: The solution proposed in this tip explores two SQL Server commands that can help us achieve the expected results.

I have solved my problem with the following query for those that have the same problem and do not have access to STRING_AGG which is introduced in SQL Server 2017:

SELECT 
    [P].[KEY],
    [PROPERTY_DESCRIPTION] = STUFF((
          SELECT ' ' + [P2].[DESC]
          FROM [dbo].[PROP_DESC] [P2]
          WHERE [P].[KEY] = [P2].[KEY]
          FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 1, 1, '')
 FROM [dbo].[PROP_DESC] [P]
 WHERE [P].[KEY] = '5962417474'
 GROUP BY [P].[KEY]

Rolling up multiple rows into a single row and column for SQL , How to Rollup Multiple Rows into a Single Row in SQL Server For each value from the inner query we are concatenating a ";" and then the� SELECT Countries = STUFF ( (. SELECT ',' + CountryName. FROM Application.Countries. FOR XML PATH ('') ), 1, 1, '') 2. Concatenate Rows Using COALESCE. You can concatenate rows into single string using COALESCE method. This COALESCE method can be used in SQL Server version 2008 and higher.

There are many ways to do it in SQL server:

Below is one way:

SELECT  key
            ,STUFF((SELECT '| ' + CAST(prop_desc AS VARCHAR(MAX)) [text()]
            FROM PROP_DESC 
            WHERE key = t.key
            FOR XML PATH(''), TYPE)
            .value('.','NVARCHAR(MAX)'),1,2,' ') prop_desc
            FROM PROP_DESC t
            GROUP BY key

Concatenate Multiple Rows Within Single Row in SQL Server 2008, STUFF Function in SQL Server. Create a database. Create 2 tables as in the following. Execute this SQL Query to get the student courseIds separated by a comma. USE StudentCourseDB. SELECT StudentID, CourseIDs=STUFF. ( ( SELECT DISTINCT ', ' + CAST(CourseID AS VARCHAR(MAX)) FROM StudentCourses t2. Concatenate multiple rows into a single row – Learn more on the SQLServerCentral forums

Concatenating Row Values in Transact-SQL, This video will show you how to concatenate multiple rows into a single string in SQL SERVER Duration: 3:21 Posted: Jan 2, 2018 stuff.zip STUFF Function in SQL Server We can concatenate multiple rows within a single row using the predefined function STUFF available in SQL Server. Here is the example.

SQL Server - Concatenate Multiple Rows Into Single String, I'm assuming adding this functionality to SQL Server opens a can of worms, In any event, below is the code I used in case anyone else finds this useful even� FOR XML PATH Clause Will will make use of the XML functionality of SQL Server to amalgamate the series of rows into one row. In order to create a XML string you must append the FOR XML command after any regular query. There are four options to append to the FOR XML PATH command which will alter what the result will look like.

Combine column from multiple rows into single row, In this post, we are gonna see how to concatenate multiple rows into one single text string in MS SQL Server. I've got some customer_comments split out into multiple rows due to database design, and for a report I need to combine the comments from each unique id into one row. I previously tried something working with this delimited list from SELECT clause and COALESCE trick but I can't recall it and must not have saved it.

Comments
  • Does this answer your question? How to concatenate text from multiple rows into a single text string in SQL server?
  • @DeanOC It does indeed. My search terms were not "accurate" in describing my problem. Once I looked over at STRING_AGG I found other users linking solutions with STUFF, FOR XML, PATH() and have answered my own question prior to the link you posted.
  • The OP has specifically tagged SQL Server, which does not support GROUP BY {ordinal}, and does not have a GROUP_CONCAT function (it's STRING_AGG)
  • Thank you both, I was able to figure it out by doing a search for STRING_AGG.
  • I have already used STUFF, FOR XML, PATH() to solve my problem. I am interested in the other ways you mentioned. Could you provide an example?