This is the dynamic one. I prefer not to do dynamic SQL in my projects but sometimes it is unavoidable. Maybe see if you can do something on the client side rather than doing dynamic SQL.

In any case, here follows the query:

DECLARE @COLUMNS INT

DECLARE @SQLSTR NVARCHAR(MAX)

DECLARE @I INT

SELECT @I = 2

--GET THE NUMBER OF ALIASES WE WILL HAVE TO ITERATE OVER

;WITH CTE_APPS

AS

(

SELECT

COUNT(*) AS COUNTED

FROM #apps

GROUP BY Applicationid

)

SELECT @COLUMNS = MAX(COUNTED) FROM CTE_APPS

SELECT @SQLSTR = '

;WITH CTE_PIVOT

AS

(

SELECT

Applicationid,

CASE SOURCE WHEN ''AR'' THEN applicationname ELSE NULL END AS applicationname,

'

--1ST LOOP TO DO THE PIVOT

WHILE @I <= @COLUMNS

BEGIN

SELECT @SQLSTR = @SQLSTR + 'CASE ROW_NUMBER() OVER ( PARTITION BY Applicationid ORDER BY CASE SOURCE WHEN ''AR'' THEN 0 ELSE 1 END ) WHEN ' + CONVERT(VARCHAR,@I) + ' THEN applicationname ELSE NULL END AS ALIAS' + CONVERT(VARCHAR,@I - 1 )

IF @I < @COLUMNS SELECT @SQLSTR = @SQLSTR + ',

'

SELECT @I = @I + 1

END

SELECT @SQLSTR = @SQLSTR + '

FROM #apps

)

SELECT

MAX(applicationname) AS applicationname,

'

--LOOP TO CREATE THE COLUMN LIST

SELECT @I = 1

WHILE @I < @COLUMNS

BEGIN

SELECT @SQLSTR = @SQLSTR + 'MAX(CTE_PIVOT.ALIAS' + CONVERT(VARCHAR,@I) + ') AS ALIAS' + CONVERT(VARCHAR,@I)

IF @I < @COLUMNS - 1 SELECT @SQLSTR = @SQLSTR + ',

'

SELECT @I = @I + 1

END

SELECT @SQLSTR = @SQLSTR + '

FROM CTE_PIVOT

GROUP BY Applicationid '

PRINT @SQLSTR

EXEC SP_EXECUTESQL @SQLSTR