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...