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.