Return sql results as a single string

Question

I have the following query which returns more than 1 result (max is about 10rows not more!), how can I return all results as a single string?

SELECT DISTINCT(e.Username)
FROM TblLeaveEmployee l
JOIN TblLeaveApprovalsBy a on l.EmployeeId = a.UserID
JOIN TblEmployee e on l.EmployeeId = e.id
where l.EmployeeId IN 
  (select UserID 
   FROM TblLeaveApprovalsBy 
   WHERE ApprovalUserID IN
     (SELECT ApprovalUserID from TblLeaveApprovalsBy where UserID = 77))

Have tried using STUFF as follows but its not working:

DECLARE @CodeNameString varchar(100)
SELECT 
   @CodeNameString = STUFF(
SELECT DISTINCT(e.Username)
FROM TblLeaveEmployee l
JOIN TblLeaveApprovalsBy a on l.EmployeeId = a.UserID
JOIN TblEmployee e on l.EmployeeId = e.id
where l.EmployeeId IN 
  (select UserID 
   FROM TblLeaveApprovalsBy
   WHERE ApprovalUserID IN
     (SELECT ApprovalUserID from TblLeaveApprovalsBy where UserID = 77)))

try this [http://stackoverflow.com/questions/17990986/concatenate-rows-for-one-column-sql](http://stackoverflow.com/questions/17990986/concatenate-rows-for-one-column-sql) — Bhavesh Parekh, Dec 11 '13 at 09:24

Jayvee · Accepted Answer · 2013-12-11T09:55:45.350

1

   DECLARE @CodeNameString VARCHAR(MAX)=NULL
   SELECT @CodeNameString = COALESCE(@CodeNameString+',' ,'') + Username 
   FROM
   (
   SELECT DISTINCT(e.Username) Username
   FROM TblLeaveEmployee l
   JOIN TblLeaveApprovalsBy a on l.EmployeeId = a.UserID
   JOIN TblEmployee e on l.EmployeeId = e.id
   where   l.EmployeeId IN (select UserID FROM TblLeaveApprovalsBy
              WHERE ApprovalUserID IN(SELECT ApprovalUserID from TblLeaveApprovalsBy    where UserID = 77))) x
   SELECT @CodeNameString

edited Dec 11 '13 at 09:55

answered Dec 11 '13 at 09:41

Jayvee

10,670
3
29
40

Msg 156, Level 15, State 1, Line 11 Incorrect syntax near the keyword 'SELECT'. – rikket Dec 11 '13 at 09:43
That's within your query, I just wrapped it and added the first three lines and the last line. Try running the inner query and see if you can find the problem there – Jayvee Dec 11 '13 at 09:47
Ok, I see what the problem is; just edited it (put an x to alias the inner query) – Jayvee Dec 11 '13 at 09:56

score 1 · Answer 2 · answered Dec 11 '13 at 09:51

DECLARE t CURSOR FOR  
SELECT DISTINCT(e.Username)
FROM TblLeaveEmployee l
JOIN TblLeaveApprovalsBy a on l.EmployeeId = a.UserID
JOIN TblEmployee e on l.EmployeeId = e.id
where l.EmployeeId IN 
(select UserID 
   FROM TblLeaveApprovalsBy 
   WHERE ApprovalUserID IN
     (SELECT ApprovalUserID from TblLeaveApprovalsBy where UserID = 77))

open t

declare @v varchar(255)
declare @Res varchar(max)
 set @Res = ''
 set @v = ''
FETCH NEXT FROM t into @v
while (@@FETCH_STATUS = 0) begin
    set @Res = @Res + @v + '|'
    FETCH NEXT FROM t into @v
end
print @Res

close t
deallocate t

score 0 · Answer 3 · answered Dec 11 '13 at 09:31

0

This will be a problem if you have huge amount of row.

Try This

DECLARE t CURSOR FOR  
select Col1 from [Table] where crite1 = ???

open t

declare @v varchar(255)
declare @Res varchar(max)
 set @Res = ''
 set @v = ''
FETCH NEXT FROM t into @v
while (@@FETCH_STATUS = 0) begin
    set @Res = @Res + @v + '|'
    FETCH NEXT FROM t into @v
end
print @Res

close t
deallocate t

answered Dec 11 '13 at 09:31

Jade

2,972
1
11
9

max numbers of rows returned are note more than 10 – rikket Dec 11 '13 at 09:38
Then this goes well just replace this query [select Col1 from [Table] where crite1 = ???] with your query – Jade Dec 11 '13 at 09:42

score 0 · Answer 4 · answered Oct 21 '22 at 02:50

The STRING_AGG is new function is available from SQL Server 2017 onwards.

SELECT STRING_AGG( ISNULL(ColumnName, ' '), ',') As ColumnABC
       From TableName

The simplest and straight forward way to concatenate rows into a string value is to use FOR XML PATH in a select query. This FOR XML PATH method can be used in SQL Server version 2005 and higher.

SELECT SUBSTRING((
            SELECT ',' + DISTINCT (e.Username) AS 'data()'
            FROM TblLeaveEmployee l
            JOIN TblLeaveApprovalsBy a ON l.EmployeeId = a.UserID
            JOIN TblEmployee e ON l.EmployeeId = e.id
            WHERE l.EmployeeId IN (
                    SELECT UserID FROM TblLeaveApprovalsBy
                    WHERE ApprovalUserID IN (SELECT ApprovalUserID FROM TblLeaveApprovalsBy WHERE UserID = 77)
                    )
            ) FOR XML path(''))
    ,2
    ,999 ) AS CodeNameString

Return sql results as a single string

4 Answers4