This is how to do it if you know how many aliases you are going to have:

;WITH CTE_PIVOT

AS

(

SELECT

Applicationid,

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

CASE ROW_NUMBER() OVER ( PARTITION BY Applicationid ORDER BY CASE SOURCE WHEN 'AR' THEN 0 ELSE 1 END ) WHEN 2 THEN applicationname ELSE NULL END AS ALIAS1,

CASE ROW_NUMBER() OVER ( PARTITION BY Applicationid ORDER BY CASE SOURCE WHEN 'AR' THEN 0 ELSE 1 END ) WHEN 3 THEN applicationname ELSE NULL END AS ALIAS2,

CASE ROW_NUMBER() OVER ( PARTITION BY Applicationid ORDER BY CASE SOURCE WHEN 'AR' THEN 0 ELSE 1 END ) WHEN 4 THEN applicationname ELSE NULL END AS ALIAS3

FROM #apps

)

SELECT

MAX(applicationname) AS applicationname,

MAX(CTE_PIVOT.ALIAS1) AS ALIAS1,

MAX(CTE_PIVOT.ALIAS2) AS ALIAS2,

MAX(CTE_PIVOT.ALIAS3) AS ALIAS3

FROM CTE_PIVOT

GROUP BY Applicationid

The ROW_NUMBER() function returns a row number (makes sense somehow!!) for every row that is returned. I am using partition by to reset the row numbering after every application id. I am also ordering it by a case statement to make sure the application name is the first row returned. Then I just do a case statement that puts the application names in the correct columns, and then I do a group by in the final query to roll the rows up.

Dynamic query to follow later...