Consolidate multiple records into a single row

I worked on a restore script that had to consume values from the network share and produce a restore statement. This database happened to be striped to eight files. I needed a way to construct the restore statement into a single row and I was able to achieve the desired results using the following syntax.

DECLARE @temp TABLE
(
	id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
	,colors VARCHAR(30)
)

INSERT INTO @temp (colors)

VALUES ('Red')
,('Blue')
,('Green')
,('Yellow')
,('Brown');


SELECT colors

FROM @temp


SELECT 'Colors' = 'RESTORE ' + 
(SELECT colors + ';'

FROM @temp 

FOR XML PATH (''), type).value('.', 'varchar(max)')

Here’s the end results.

results

Advertisements