7

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
10

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)').

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.