CODEWITHSUNDEEP
sql-servertranspose
apple
apple

Reputation: 21

SQL Server how to turn number of rows to column value?

I have three rows return from a table as below:

select ID 
from service

Results:

ID 
--
1 
2
3

How can I return output like below:

count  |    IDs 
-------+----------
3      |    1,2,3

Upvotes: 0

Views: 36

Answers (2)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

If you are going to be work with stuff() function then you will no need to subquery for count of ids

select count(1) count,
       stuff(
             (select ','+cast(id as varchar) from table for xml path('')),
             1,1,'') Ids
from table

Upvotes: 0

Raghu Ariga
Raghu Ariga

Reputation: 1129

hope this helps

select (select Count(*) from service)+' | '+ SELECT STUFF
(
    (
        SELECT ',' + s.FirstName 
        FROM Employee s
        ORDER BY s.FirstName FOR XML PATH('')
    ),
     1, 1, ''
) AS Employees)

Upvotes: 1

Related Questions