CODEWITHSUNDEEP
sqlsql-serverconcatenationaggregate-functions
Erick Ramirez
Erick Ramirez

Reputation: 167

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

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

Upvotes: 0

Views: 550

Answers (3)

sam
sam

Reputation: 1985

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

Upvotes: 0

Erick Ramirez
Erick Ramirez

Reputation: 167

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]

Upvotes: 0

Dina Graves Portman
Dina Graves Portman

Reputation: 151

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;

Upvotes: 2

Related Questions