Concatenate one-to-many field in single result?
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. &gt;, &amp;) they will become entitized (&gt; -> &amp;gt;, &amp; -> &amp;amp;). To avoid this, change FOR XML PATH('') to FOR XML PATH, TYPE).value(N'.[1]',N'nvarchar(max)').