Concatenate one-to-many field in single result?

Say I have the following query:

SELECT * 
FROM AppDetails, AppTags 
WHERE AppDetails.AppID = '1' 
  AND AppDetails.AppID = AppTags.AppID

Which gives the following results:

AppID    AppName        AppType    Tag
1        Application1   Utility    Test1
1        Application1   Utility    Test2
1        Application1   Utility    Test3

How can I modify the query to return something like this:

AppID    AppName        AppType    Tags
1        Application1   Utility    Test1,Test2,Test3

Unfortunately SQL Server doesn't have an single function to perform group concatenation but there are a few different ways that you can get the result.

You can implement FOR XML PATH and STUFF():

SELECT DISTINCT d.AppId,
  d.AppName,
  d.AppType,
  Tags = STUFF((SELECT ', ' + t.TagName
                FROM AppTags t
                where d.AppID = t.AppID
                FOR XML PATH (''))
                , 1, 1, '') 
FROM AppDetails d
WHERE d.AppID = '1';

See SQL Fiddle with Demo.

Or you could use FOR XML PATH with CROSS APPLY:

SELECT DISTINCT d.AppId,
  d.AppName,
  d.AppType,
  tags = left(t.tags, len(t.tags)-1) 
FROM AppDetails d
CROSS APPLY
(
  SELECT t.TagName + ', '
  FROM AppTags t
  WHERE d.AppID = t.AppID
  FOR XML PATH('')
) t (Tags)
WHERE d.AppID = '1';

See SQL Fiddle with Demo.

If you want to then query against the tags values, then you can use a CTE similar to:

;with cte as
(
  SELECT DISTINCT d.AppId,
    d.AppName,
    d.AppType,
    Tags = STUFF((SELECT ', ' + t.TagName
                  FROM AppTags t
                  where d.AppID = t.AppID
                  FOR XML PATH (''))
                  , 1, 1, '') 
  FROM AppDetails d
  WHERE d.AppID = '1'
)
select *
from cte
where tags like '%test1%'

See Demo

If TagName includes certain XML characters (e.g. >, &) they will become entitized (> -> >, & -> &). To avoid this, change FOR XML PATH('') to FOR XML PATH, TYPE).value(N'.[1]',N'nvarchar(max)').

StackExchange Database Administrators Q#50153, answer score: 11