CODEWITHSUNDEEP
sql-server
Mehdi Saghari
Mehdi Saghari

Reputation: 164

SQL Server : select merge all values with same keys

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

Upvotes: 1

Views: 1068

Answers (1)

Yitzhak Khabinsky
Yitzhak Khabinsky

Reputation: 22275

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

Upvotes: 1

Related Questions