Concatenate multiple rows and multiple columns

Transact-SQL
1

Hi ,
What would you suggest I use to accomplish this?

From this:
|Range | Sys |
|1245-1250| 33FT |
|1251-1259| 34FT |

To this:
|Range | Sys |
|1245-1250,1251-1259| 33FT,34FT |

Thanks!

2 
declare @table table(Range varchar(255),Sys varchar(255))

insert into @table values
('1245-1250','33FT'),
('1251-1259','34FT')

select * 
from @table


select Distinct 
'|' +stuff(Table1.Range1,1,1,'') + '| ' + stuff(Table2.Sys1,1,1,'')+ ' |'  as New_column 

from @table a
cross apply (
				select 
				', '+ Range
				from @table
				for xml path('')
)Table1 (Range1)
cross apply (
				select 
				', '+ Sys
				from @table
				for xml path('')
)Table2 (Sys1)
1 Like
3

The example you gave is not realistic. Unless the table contains only a few records the result will be close to useless. All the data from all the records in the source table will always end in 1 record. A small source table with only 1000 records will result in a very very wide string.

In reality you will want to represent your data grouped by something. Maybe you want to put all the cars a family possessed into one data field (for brevity). Most of the time you will also want to order the cars by something, like date of purchase.

I have reused your example, and added a group of some kind, like a family. It's called "a_group".

(I shamelessly used part of Muj9's code)

declare @table table(Range varchar(255),  Sys varchar(255), a_group varchar(255) )

insert into @table values
('1245-1250','33FT', 'A'),
('1251-1259','34FT', 'A'),

('245-250','33FL', 'B'),
('251-259','34FL', 'B');

select * 
from @table;

A working solution when your SQL version is older than SQL Server 2017

;WITH CTE AS (
SELECT a_group
FROM @table
GROUP BY a_group
)
SELECT 
	CTE.a_group
	,Ranges = STUFF((SELECT ', ' + t.Range 
						FROM @table AS t 
						WHERE CTE.a_group = t.a_group
						ORDER BY t.Range, t.Sys
						FOR XML PATH('')
						), 1, 2, N''
					)
	,Syses = STUFF((SELECT ', ' + t.Sys 
						FROM @table AS t 
						WHERE CTE.a_group = t.a_group
						ORDER BY t.Range, t.Sys
						FOR XML PATH('')
						), 1, 2, N''
					)
FROM CTE
ORDER BY CTE.a_group;

A working solution if your SQL Server version is SQL Server 2017 or later

;WITH CTE AS (
SELECT a_group
FROM @table
GROUP BY a_group
)
SELECT 
	CTE.a_group
	,Ranges = STRING_AGG(t.Range, ', ')
				WITHIN GROUP (ORDER BY t.Range, t.Sys)
	,Syses = STRING_AGG(t.Sys, ', ')
				WITHIN GROUP (ORDER BY t.Range, t.Sys)

FROM CTE
	INNER JOIN @table AS t
		ON CTE.a_group = t.a_group
GROUP BY CTE.a_group
ORDER BY CTE.a_group;
4

Hi,

This works fine for what I wish to accomplish.
The character '|' was only meant to be a separator for column,
trying to display a table with some data. Sorry for the confusion.

Thanks a lot!

5

Hi,

Yes the end result will always have one record
So @table with values like this
('1245-1250','33FT'),
('1251-1259','34FT'),
('1260-1279','35FT'),
('1280-1296','36FT'),
Would become @table with values like this
('1245-1250,1251-1259, 1260-1279,1280-1296','33FT,34FT,35FT,36FT')
It is like a concatenation on each column.

Thanks!