Getting a list of text concatenated in a group by

Question

Say I have this data:


site cell value
a    b    "1"
a    c    "2"

And I want the output for the format:


site value
a    "b=1,c=2"

Is it possible with SQL?

PS: I am using access. But even if access does not support this particular syntax I would like to know any database that can.

It is possible to write a VBA function that takes a `site` and returns the concatenated string, reading off a private recordset. This function can then be used from SQL (within Access, not via a data access library such as ADODB or ADO.NET). Does that count? — Zev Spitz, Feb 25 '15 at 12:17

score 2 · Answer 1 · answered Mar 15 '11 at 09:05

2

It is possible to do this in MySQL with GROUP_CONCAT

answered Mar 15 '11 at 09:05

Ocaso Protal

19,362
8
76
83

score 2 · Accepted Answer · answered Mar 15 '11 at 09:28

Declare @tbl table ([site] nvarchar(100),Cell nvarchar(100),Value nvarchar(100))
INSERT INTO @tbl values('A','b','1')
INSERT INTO @tbl values('A','c','2')

SELECT [Site],
SUBSTRING(
(
select ' ,'+  Cell +'=' + CAST(value AS VARCHAR)
from @tbl b
WHERE a.[Site] = b.[Site]
FOR XML PATH('')
)
,3,100)

FROM @tbl a
GROUP BY a.[Site]

Getting a list of text concatenated in a group by

2 Answers2

Linked