declare @SQL nvarchar(max), @Cols nvarchar(max), @Cols1 nvarchar(max)

select @Cols = stuff((

SELECT ',' + quotename(column_name)
FROM information_schema.columns
WHERE table_name = 'unsaccountb'
AND COLUMN_NAME <> 'XObjectKey'
AND COLUMN_NAME NOT LIKE 'UID%' -- exclude columns starting with UID
FOR XML PATH('')),1,1,'')

select @Cols1 = stuff((

SELECT ',CONVERT(Varchar(max),' + quotename(column_name) + 
') AS ' + quotename(COLUMN_NAME)
FROM information_schema.columns
WHERE table_name = 'unsaccountb'
AND COLUMN_NAME <> 'XObjectKey'
AND COLUMN_NAME NOT LIKE 'UID%' -- exclude columns starting with UID
FOR XML PATH('')),1,1,'')

---set @SQL = '
;WITH NewValues AS
(
	SELECT XObjectKey, FieldName, FieldValue
	FROM (select xObjectKey, ' + @Cols1 + ' FROM [unsaccountb] ) Src
	
	UNPIVOT
	(
		FieldValue FOR FieldName IN ( '+ @Cols +' )
		
	) as x
)

quote:

---

Originally posted by nigelrivett

would yoou expect
select xObjectKey, ' + @Cols1 + ' FROM [unsaccountb]

to be valid?
Are yoou trying to create a string to execute? if so yoou need to uncomment the set statement and add a quote at the end.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

---