Sorry for the delay, I've been off on vacation for a couple weeks

Try the following:

eclare @query nvarchar(max);

-- build small inline tally table

WITH E(N) AS(

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1

)

,cteTally(N) AS( -- In this case, we are having the tally move in increments of 4 starting with an offset. For this example I start at column #21

SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL))*4+17

FROM E, E x

),getpairs AS ( -- Now we have to figure out the columns that go together in the unpivoted table, and concatenate them

select n,STUFF ((

SELECT ',' + CASE WHEN c.colid = t.N THEN '''' + c.name + ''',' + '['+c.name+']' ELSE '['+c.name+']' END

FROM sys.syscolumns c

JOIN sys.sysobjects o

ON o.id=c.id

WHERE o.xtype='u'

AND o.name=N'ObstetricsGynecology'

AND c.colid IN (t.N,t.N+1,t.N+2)

AND c.colid < (SELECT MAX(colid)-4 FROM sys.syscolumns c2 WHERE c2.id = o.id)

FOR XML PATH('')

),1,1,'') pairs

FROM cteTally t

GROUP BY n

) -- Now we concatenate the groups of pairs into a larger string with the rest of the query

SELECT @query=N'select [uplift specialty], [member po],[practice unit name], [final nomination status]

,[final uplift status], [final rank], [final uplift percentage]

,practiceID=row_number() over (partition by [practice unit name] order by Metricname)

,metricname,Metricvalue, metricpercentilerank,metricGPS

into aaaaaaObstetricsGynecologyUnpivotednonp

from ObstetricsGynecology

cross apply (

values ' + STUFF ((

SELECT N'),(' + pairs

FROM getpairs

WHERE pairs IS NOT NULL

FOR XML PATH ('')),1,2,'')+')) x(metricname,MetricValue, metricpercentilerank, metricGPS)'

EXEC SP_EXECUTESQL @Query

The in clause needs to add t.n+2 to get the new columns, you need to create a name for the new column in the cross apply, and then you need to ask for the new column. This ought to work.