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() 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.

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…

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 - 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).

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.