SQL Server : select merge all values with same keys

Question

I have a table in SQL Server that looks like this:

id: int
key: nvarchar(max)
value: nvarchar(max)

I want to select distinct keys as first column and all values with same key joined with '<br/>' as my second column. The key values are dynamic and is not predefined - and the performance really matters - I don't want to use Linq or any UDF!

id    key      value
---------------------------------------
1     color    red<br/>white<br/>black
4     size     15"

PS: I have searched a lot sorry if it's duplicated, currently running on SQL Server 2014 but I can move to 2019

You can search as string aggregate functions. Also check [this](https://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-server) subject — RESUL YURTTAKALAN, Jan 17 '21 at 19:17
SQL Server **2017** and newer have a built-in [`STRING_AGG`](https://learn.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-ver15) function - so upgrading to 2017 or newer would definitely be an option with a lot of potential — marc_s, Jan 17 '21 at 19:36
While asking a question, you need to provide a minimal reproducible example. Please refer to the following link: https://stackoverflow.com/help/minimal-reproducible-example Please provide the following: (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it. (3) Desired output, based on the sample data in #1 above. (4) Your SQL Server version (SELECT @@version;) — Yitzhak Khabinsky, Jan 17 '21 at 20:10
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) — Charlieface, Jan 17 '21 at 20:58

score 1 · Accepted Answer · answered Jan 17 '21 at 20:31

Please try the following solution. It will work starting from SQL Server 2008 onwards.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, [key] NVARCHAR(MAX), [value] NVARCHAR(MAX));
INSERT INTO @tbl ([key], [value]) VALUES
(N'color', N'red'),
(N'color', N'white'),
(N'color', N'black'),
(N'size', N'15"');
-- DDL and sample data population, end

DECLARE @separator CHAR(5) = '<br/>'
    , @encoded VARCHAR(20) = '&lt;br/&gt;';

SELECT c.[key]
   , STUFF(REPLACE(
   (SELECT @separator + CAST([value] AS NVARCHAR(MAX)) AS [text()]
         FROM @tbl AS O
         WHERE O.[key] = C.[key]
         FOR XML PATH('')
         ), @encoded, @separator)
         , 1, LEN(@separator), NULL) AS valueList
FROM @tbl AS c
GROUP BY c.[key];

Output

+-------+-------------------------+
|  key  |        valueList        |
+-------+-------------------------+
| color | red<br/>white<br/>black |
| size  | 15"                     |
+-------+-------------------------+

Thank, a working alternative for old sql versions. but what about performance? which one is better your xml method or as @marc_s mentioned in commnets upgrade to new Sql version!? Because I had bad performance experience to use some new sql build in functions like Pivot ! — Mehdi Saghari, Jan 17 '21 at 21:26
Just give it a shot with your current version. If its performance is acceptable so no need to upgrade. — Yitzhak Khabinsky, Jan 17 '21 at 22:25

SQL Server : select merge all values with same keys

1 Answers1