sqlstored-proceduressql-server-2005temp-tablestable-variable

Sending the stored procedure output as comma-separated rather than multiple rows


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


Solution

  • 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