How to concatenate rows without cursor?

Transact-SQL
1

I am gathering data on building permits. I have a business request to reduce each permit's data to one row. Currently, I have an issue where I have multiple lots under each permit, with one row per lot.

Permit Lot
A 1
A 2
A 3

What is the best way to change the output to:

Permit Lot
A 1,2,3

The only way I can think to do it is to use a cursor. Does anyone have a better way?

2

This should perform far better than a cursor...

IF OBJECT_ID('tempdb..#TestData', 'U') IS NOT NULL 
DROP TABLE #TestData;

CREATE TABLE #TestData (
	Permit CHAR(1) NOT NULL,
	Lot TINYINT NOT null
	);

INSERT #TestData (Permit, Lot) VALUES
	('A', 1), ('A', 2), ('A', 3),
	('B', 1), ('B', 2), ('B', 3),
	('C', 1), ('C', 2), ('C', 3),
	('D', 1), ('D', 2), ('D', 3),
	('E', 1), ('E', 2), ('E', 3);

--SELECT * FROM #TestData td;

--==========================================

SELECT 
	td1.Permit,
	LotList = STUFF(ll.LotList, 1, 2, '')
FROM
	#TestData td1
	CROSS APPLY (
			SELECT 
				CONCAT(', ', td2.Lot)
			FROM
				#TestData td2
			WHERE 
				td1.Permit = td2.Permit
			FOR XML PATH ('')
			) ll (LotList)
GROUP BY
	td1.Permit,
	ll.LotList;
1 Like
3

The "standard" approach in SQL is to use "FOR XML". You can Google example(s) of that technique.

1 Like
4

Jason, after a little tweaking, that worked great. I've been coding in SQL for nearly 20 years and this is the first time, believe it or not, that I've ever needed to use CROSS APPLY. Although I got the code to work, I'm still trying to wrap my head around the concept. But thanks for the help.

5

No problem...

In this case the CROSS APPLY is optional. It could have just as easily been done as a correlated sub-query in the SELECT list.
That said, once you get used to using the APPLY operators, they'll come as naturally as JOIN syntax and you'll wander how you ever survived without them.

The basic gist... They basically allow you to create correlated sub-queries in the FROM clause... Which isn't possible with JOINs.