CODEWITHSUNDEEP
sqlsql-server
sourabhgkr
sourabhgkr

Reputation: 89

SQL Server select resultset values as a comma-separated string

I have the following query in a stored procedure.

set @result1='EmpId,EmpName,Phone,City'

set @result2='select '+ @result1+ ' from [emptable]'+' where EmpId=1 and
EmpjoinDate= ''May-2014'''

exec(@result2)

The query returns

EmpId | EmpName | Phone  | City
----------------------------------   
  1   | John    | 832942 | NewYork

Now how to add query so that stored procedure returns single column result

| EmpInfo .........................|
------------------------------------    
1,John,832942,NewYork

Please reply. Thanks.

Upvotes: 1

Views: 825

Answers (5)

Darka
Darka

Reputation: 2768

What you can do, is after receiving @result1 you replace data as you wish:

set @result1='EmpId,EmpName,Phone,City'


SET  @result1  = REPLACE (@result1, ',', ' + '','' + ')

--SET  @result1  = 'CONCAT ( ' + REPLACE (@result1, ',', ' , '','' , ') + ')'   -- OR THIS IF you have INT inside this columns


set @result2='select '+ @result1+ ' from [emptable]'+' where EmpId=1 and
EmpjoinDate= ''May-2014'''

exec(@result2)

With CONCAT function looks better, if you have Server 2012. If not you will need CAST INT values into String

Upvotes: 0

Deepshikha
Deepshikha

Reputation: 10284

Using For XML and STUFF re-write the query as:

declare @result1 varchar(max), 
        @result2 varchar(max) 
--Modify all input columns using Cast function to cast int datatype columns 
--to varchar .Also this way you can add as many columns from table 'emptable'
--as you want

set @result1=''','' + cast(EmpId as varchar(20)) + '','' + EmpName + '','' 
    + cast(Phone as varchar(20)) + '','' + City'

set @result2='select STUFF((SELECT '+ @result1+ '  from [emptable] 
    '+' where EmpId=1 FOR XML PATH('''')), 1, 1, '''') as  EmpInfo'

exec(@result2)

Check Demo here

Upvotes: 0

Goon10
Goon10

Reputation: 150

set @result1='CONCAT(EmpId,',',EmpName,',',Phone,',',City)'

set @result2='select '+ @result1+ ' as EmpInfo from [emptable]'+' where EmpId=1 and
EmpjoinDate= ''May-2014'''

exec(@result2)

Upvotes: 2

Bis.dexterity
Bis.dexterity

Reputation: 11

If I get you clearly then this is who you want. You have to concatenate the fields with the commas

set @result1='EmpId+'',''+EmpName''+'',''+''Phone''+'',''+''City'

set @result2='select '+@result1+' as EmpInfo from [emptable]'+' where EmpId=1 and
EmpjoinDate= ''May-2014'''

exec(@result2)

Upvotes: 0

Martin K.
Martin K.

Reputation: 1060

You have to build the string in the @result1 variable:

set @result1='EmpId+'',''+EmpName+'',''+Phone+'',''+City'

In Management Studio, you can also go to the Query Menu and select "Results To" -> "Results to Text" if that is enough for you.

Upvotes: 0

Related Questions