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

Question

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

Raghu Ariga · Accepted Answer · 2018-02-19 00:57:13Z

1

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)

answered Feb 19, 2018 at 0:57

Raghu Ariga

1,1491 gold badge19 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dan Guzman Over a year ago

@apple, mark this as the answer since it answered your question.

Raghu Ariga Over a year ago

@apple Glad i could help

Yogesh Sharma · Accepted Answer · 2018-02-19 05:18:56Z

0

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

answered Feb 19, 2018 at 5:18

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related