Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question

1 Answer 1

up vote 5 down vote accepted

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

share|improve this answer
    
I like the STUFF version, much simpler and works great. Thanks! –  Scott Beeson Sep 18 '13 at 20:53
    
Hmm, I can't use "tags" as a comparison now, any ideas? (For instance, in the example, if I add "AND (Tags LIKE '%test1%')" it says it's an invalid column name. –  Scott Beeson Sep 18 '13 at 21:03
    
The comments section in this answer might be of interest regarding the use of distinct. But here, the distinct is probably not necessary at all since AppID looks like the PK in AppDetails. –  Mikael Eriksson Sep 18 '13 at 21:04
    
@ScottBeeson If you want to reference the tags in a WHERE clause, then look at using a CTE, see my edit –  bluefeet Sep 18 '13 at 21:06
    
@MikaelEriksson Thanks for that info, I was not aware of that either. –  bluefeet Sep 18 '13 at 21:07

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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