How do you add line returns after every nth item in a string?
sql-server add tag
James 
So, my favorite time saver of all time:

>   
> create procedure [sp_get_columns] (@table nvarchar(255), @schema nvarchar(255) = 'dbo')  
>   
> as  
>   
> select stuff( (  
> 			select ', ' + t1.Column_Name  from information_schema.columns t1	  
> 			where t1.Table_Name = @table and t1.TABLE_SCHEMA = @schema   
> 			for xml path ('')),1,2,'') as columns

            
will return results that look like so:


> CASE_FCT_ID, CASE_BK, PTNT_DIM_ID, SRC_PTNT_ID, SRC_CARE_EVNT_CNTR_TXT, ANES_TP_DIM_ID, CASE_CRT_TS, CASE_STRT_TS


Problem: there's often 50 or more columns. In my old age, I have started to love grouping my columns 5 per row, in 2 rows, then a space. I find this makes it easy to count columns and see that they match.

Question:

How would you manipulate the string get it to look like this:


> col1, col2, col3, col4, col5,  
> col6, col6, col8, col9, col10,  
>   
> col11, col12, col13, col14, col15,  
> col16, col17, col18, col19, col20,  
>   
> col21, col22, col23, col34, col25,  
> col26, col27, col28, col29, col30
Top Answer
Paul White 
If you're using the *Results to Text* output option in SSMS, the following procedure might be suitable:

<>https://dbfiddle.uk/?rdbms=sqlserver_2014&fiddle=9e05a9072ebaff40613421d7c3641d1d&hide=1
Answer #2
PeterVandivier 
# [`string_agg()`][1] is best agg

If you can get away from the `for xml path` syntax, `string_agg()` (introduced in SQL Server 2017) offers a more readable syntax to achieve this and more. 

The most naïve example of this replacement can be seen in the following fiddle:

<>https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=c405033c67eeeeb6b47d950b2c5173ce

In about the same number of lines as `xml path`, `string_agg` will allow you to add an element by which you can group and subsequently split your otherwise concatenated identifiers. A more functional (but less embeddable) version of this fiddle can be seen [here][2].

---

You will note some interim caching in version 2 of the fiddle, this is helpful not only for debugging but also for further prettification options. For example, if you use this same logic against the practical sample set you provided, [you get the following][3]... 

```
CASE_FCT_ID, CASE_BK, PTNT_DIM_ID, SRC_PTNT_ID, SRC_CARE_EVNT_CNTR_TXT, 
ANES_TP_DIM_ID, CASE_CRT_TS, CASE_STRT_TS
```

This does strictly put 5 columns per line, but in practice maybe you want to line wrap at 50 characters or something instead. Say you have highly variable lengths in your column names. You could instead [do something like this][4] - in which you can see columns filling out to 50 characters and then wrapping to the newline (this does come with its own set of implementation quirks though).

[1]: https://docs.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql
[2]: https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=5636f8009c1b5720685e763be6674416&hide=1 
[3]: https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=e4a13ea1ff3f0d449bcfabc6880679c7&hide=3
[4]: https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=81fc0fdf5fbc91600a0eb715d4484c80
comments
transcript

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.