Getting a list of text concatenated in a group by
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 n开发者_开发百科ot support this particular syntax I would like to know any database that can.
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]
It is possible to do this in MySQL with GROUP_CONCAT
精彩评论