I can see several issues in this code, but I'll just deal with the question at hand:
For a start,
NVARCHAR(4000)
has not been the standard since SQL 2000 (maybe 2005). Use
NVARCHAR(MAX)
instead. It is variable length so is more efficient for strings of unknown length.
Without (4000) or (MAX) the default for NVARCHAR is (30). Hence
CONVERT(NVARCHAR, '1470, 1471, 1472, 1474, 1475, 1477, 1478, 1480')
=
'1470, 1471, 1472, 1474, 1475, '
set the size of the NVARCHAR to fix the issue, but it is already a string so you don't even need to convert it.
Also: Coalesce is used to ignore null values. Your code excludes the possibility of nulls so this is pointless
This is what your code should look like to do what it is written to do:
DECLARE @AssignedGroupIDs AS NVARCHAR(4000)
SELECT
@AssignedGroupIDs= '1470, 1471, 1472, 1474, 1475, 1477, 1478, 1480, ' + Convert(NVARCHAR,tbGroup.GroupID )
FROM
tbGroup,tbAssignedGroups
WHERE
tbGroup.GroupID = tbAssignedGroups.GroupID
AND tbAssignedGroups.UserID = 2
AND tbGroup.IsDeleted = 0
select @AssignedGroupIDs
Perhaps this is not what you intended, but it's what you wrote.
If you need help using COALESCE then let mw know and I'll help you put together what you need.
Hope that helps ^_^
Andy