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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s