Ok, so lets add in a condition to ignore the last 5 columns. If these are always the last 5 it should work.

DECLARE @Query nvarchar(max),

@tablename nvarchar(50)=N'Rheumatology'

;

-- First we build a 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 #8

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 your 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=@tablename

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

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

from ' + @tablename + '

cross apply (

values ' + STUFF ((

SELECT N'),(' + pairs

FROM getpairs

WHERE pairs IS NOT NULL

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

EXEC SP_EXECUTESQL @Query