Rows to comma separated, vertical to horizontal, values in SQL Server

0
votes

How to get rows as comma separated values.

select [name] from sys.objects where type='u'

i get this :

DropDownList
SubActivityDetails
Report
Employees
UserMaster
ProjectMaster

i want this :

DropDownList,SubActivityDetails,Report,Employees,UserMaster,ProjectMaster

in a single Query only

And also in this case

select [name],[object_id] from sys.objects where type='u'

I get this:

DropDownList    66099276
SubActivityDetails  197575742
Report  254623950
Employees   270624007

But I want this:

  ["SubActivityDetails",197575742],
  ["Report",254623950],
  ["Employees",270624007]

so that its similar to json.

And there would be lot of data, so performance is a major concern!

which is better

case 1 :

DECLARE @Val VARCHAR(MAX) begin select @Val = isnull(@val + ',' + [name],[name]) from sys.objects
where type='u' SELECT @Val end

case 2 :

DECLARE @Val VARCHAR(MAX) begin select @Val = COALESCE(@val + ',' + [name],[name]) from sys.objects
where type='u' SELECT @Val end

1
sqlsql-serverperformancetsqlcsv

1 Answers

5
votes

Have a look at something like this

DECLARE @Val VARCHAR(MAX)
select  @Val = COALESCE(@val + ',' + [name], [name])
from    sys.objects 
where   type='u' 

SELECT @Val

Ok, so for the second query we can try

DECLARE @Val VARCHAR(MAX)

select  @Val = COALESCE(@val + ',' + '["' + [name] + '",' + CAST([object_id] AS VARCHAR(50)) + ']', '["' + [name] + '",' + CAST([object_id] AS VARCHAR(50)) + ']') 
from    sys.objects 
where   type='u' 

SELECT @Val