I am using this to concatenate different strings into one and it works but problem is that it is UNDERLINED and BLUE like a link and I tried removing it but nothing works. Also, column header is something not presentable.
SELECT np.Name+ ', ' AS 'data()'
FROM NewsPaper np
inner join NitNewsPaper nitnp
on nitnp.NewsPaper_ID= np.NewPaperID
where Nit_NO= 1518
FOR XML PATH('')
Try the below query. It should fix your issue.
SELECT STUFF((SELECT ', ' + np.NAME FROM NewsPaper np INNER JOIN NitNewsPaper nitnp ON nitnp.NewsPaper_ID = np.NewPaperID WHERE Nit_NO = 1518 FOR XML PATH('')), 1, 2, '') as 'data'
FOR XML PATH allows you to output the results of the query as XML and hence you are seeing the blue underline. This will output something like:
, Name1, Name2. We need to get rid of the leading
', ' from this. So, we use STUFF to remove the first 2 characters.