Push all rows into single row and column

Question

I have the following query:

SELECT 
    '' + CONVERT(VARCHAR(MAX),c.ClientId) + ','
FROM [dbo].[tblClient] c

This returns 17,000 + rows. Is there a way to make all these rows return as 1 value? For example:

 6A7A24CD-061C-4653-9790-882D90F81E1D,0980722E-6E96-4498-B3BB-BFB4CA60EAC6,etc etc etc.

I am trying to use this as a parameter for testing.

possible duplicate of [concatenate many rows into a single text string?](http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string) — Alex K., Sep 09 '11 at 16:22
Is there wiki for this kind of thing yet? Must be one of the most commonly-asked questions. — Aaron Bertrand, Sep 09 '11 at 18:39

score 5 · Accepted Answer · edited May 23 '17 at 10:24

5

does this work for you?

DECLARE @v VARCHAR(MAX)
SELECT @v = ''

SELECT 
    @v = @v +  CONVERT(VARCHAR(MAX),c.ClientId) + ','
FROM [dbo].[tblClient] c
WHERE c.ClientId IS NOT NULL


SELECT  @v

Note: Just be aware that if you add an ORDER BY that it is not guaranteed to sort it, in that case use xml path as shown in Remus' answer

See also: Concatenate Values From Multiple Rows Into One Column Ordered

edited May 23 '17 at 10:24

Community

1
1

answered Sep 09 '11 at 16:18

SQLMenace

132,095
25
206
225

That will work. It wont be able to store all my values but it will work for what I need. Thanks. – mameesh Sep 09 '11 at 16:20
varchar(max) will go up to 2 billion characters – SQLMenace Sep 09 '11 at 16:22
I thought it was 8,000 characters? – mameesh Sep 09 '11 at 16:27
varchar(8000) = 8000, varchar(max) goes up to 2GB – SQLMenace Sep 09 '11 at 16:33

score 4 · Answer 2 · answered Sep 09 '11 at 16:20

4

The article covers a number of techniques at your disposal: Concatenating Row Values in Transact-SQL. My favorite technique is the black-box XML method:

SELECT cast(c.ClientId as varchar(20)) + ','
FROM [dbo].[tblClient] c
for xml path(''), type;

answered Sep 09 '11 at 16:20

Remus Rusanu

288,378
40
442
569

2

+1 The other method will possibly break if an `ORDER BY` clause is added as [described here](http://support.microsoft.com/kb/287515/en-us) – Martin Smith Sep 09 '11 at 16:31
Yes ORDER is not guaranteed, since I didn't see ORDER BY in his query I gave him this version....I have both versions here: http://wiki.lessthandot.com/index.php/Concatenate_Values_From_Multiple_Rows_Into_One_Column_Ordered...I will add this to my answer just in case – SQLMenace Sep 09 '11 at 16:35
There is no order by. I just needed to use it to populate a parameter list. – mameesh Sep 09 '11 at 21:15

Push all rows into single row and column

2 Answers2