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

Question

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 |
+------------+---------------------------------------------------+----------+

Does this answer your question? [How to concatenate text from multiple rows into a single text string in SQL server?](https://stackoverflow.com/questions/194852/how-to-concatenate-text-from-multiple-rows-into-a-single-text-string-in-sql-serv) — DeanOC, Nov 27 '19 at 22:29
@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. — Erick Ramirez, Nov 27 '19 at 22:32

Dina Graves Portman · Answer 1 · 2019-11-27T22:15:18.540

2

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;

edited Nov 27 '19 at 22:15

answered Nov 27 '19 at 22:10

Dina Graves Portman

151
5

1

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`) – Thom A Nov 27 '19 at 22:11
Thank you both, I was able to figure it out by doing a search for `STRING_AGG`. – Erick Ramirez Nov 27 '19 at 22:28

score 0 · Answer 2 · answered Nov 27 '19 at 22:25

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]

score 0 · Answer 3 · answered Nov 27 '19 at 22:28

0

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

answered Nov 27 '19 at 22:28

sam

1,937
1
8
14

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? – Erick Ramirez Nov 27 '19 at 22:29

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

Overview

Desired Output

3 Answers3