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

Question

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

Would you please edit your question to show what the expected output looks like, according to the sample data you provided? — GMB, Mar 08 '19 at 00:55
That sort of thing is best done in your app code. Any particular reason you want to do it in SQL? — Dale K, Mar 08 '19 at 01:07
With SQL Server 2017, you can do this using [STRING_AGG](https://docs.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-2017) — Luis Cazares, Mar 08 '19 at 02:10
@GMB: Thats exactly what I vaugely understood from people. I would clarify again and post. — Jasmine, Mar 08 '19 at 02:27
@DaleBurrell: Its a good question, the front end (Which is MULESOFT) needs in this format for some reason. They can do in their end as well, but asked if I can do in SP. Any reason that we shouldn't do in SQL And or you have strong reason to do in Code mule? — Jasmine, Mar 08 '19 at 02:28
@LuisCazares: I use SQL 2005, good point you raised, I will update in question, so I need solution for 2005 — Jasmine, Mar 08 '19 at 02:29
I've voted to close as unclear what is required. If expected output (and ideally a MCVE) are posted I'll remove it. — DeanOC, Mar 08 '19 at 02:50

Sujit.Warrier · Accepted Answer · 2019-03-11T11:28:14.130

1

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

edited Mar 11 '19 at 11:28

answered Mar 08 '19 at 03:09

Sujit.Warrier

2,337
2
25
38

Thank you, but I dont want the Travel to be repeated as its a input parameter and also same value for all rows. Any better way not to repeat the first column alone? – Jasmine Mar 10 '19 at 20:42
so you dont want travel to be part of the returned results right? – Sujit.Warrier Mar 11 '19 at 11:27
No, I want that. I don't want that to be repeated. I want only once, as it is same value. And, apparently its only one row now we are returning? So that can be present at return value. please help – Jasmine Mar 11 '19 at 21:34
1

we are returning the string containing all rows as one record, in your code get that column and do whateveryou want with it. – Sujit.Warrier Mar 12 '19 at 01:59
Thank you Sujit, will do and let you know how I go and if I hit any problem. Cheers – Jasmine Mar 12 '19 at 03:28

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

1 Answers1