I have a stored procedure that returns multiple rows with 5 columns:
SELECT Travel, ID, Dept, Role, Country
FROM TravelManager
For example, I get:
DName ID Dept Role Country
----------------------------------
Travel 23 HR H USA
Travel 29 MR M AUS
Travel 32 FI M UK
Is there any way for me to send this as comma-separated values as first column is same, I want to send only one row without duplicating names in multiple rows and also send other columns as one row only as coma separated values.
Any thoughts and better way to do this? Also how would I modify my select query?
My first column is stored procedure input, so returning that as well :)
I was thinking of temp table or table variable but how to do if so? Please help
It can be done by using a cursor
declare @Travel nvarchar(max)
declare @ID int
declare @Country nvarchar(max)
declare @Dept nvarchar(max)
declare @Role nvarchar(max)
declare @string nvarchar(max)
declare cursor1 cursor for
SELECT ID, Dept, Role, Country
FROM TravelManager
set @string = '';
open cursor1
fetch next from cursor1 into @ID, @Dept, @Role, @Country
while (@@Fetch_Status <> -1)
begin
set @string += convert(varchar(10), @ID) + ',' +
@Dept + ',' + @Role + ',' + @Country + char(13) // char(13) for new line
fetch next from cursor1 into @ID, @Dept, @Role, @Country
end
close cursor1
deallocate cursor1
select @string as ReturnValue
char(13)
adds the carriage return else you can add \n I guess
Edit: removed selecting Travel