Nest a PIVOT query in an outer query

[waubain]

I have pivoted some data using the following sql.

USE TEST
DECLARE @sql AS varchar(MAX)
DECLARE @columns AS varchar(MAX)

SELECT @columns = 
	COALESCE(@columns + ',','') + QUOTENAME(RowID)
FROM
(
SELECT DISTINCT RowID
FROM [dbo].[DateOfBirth]
) AS B
ORDER BY B.RowID

SET @sql = '
WITH PivotData AS
(
SELECT *
FROM [dbo].[DateOfBirth]
) 
SELECT
ID
,'+ @columns +'
FROM PivotData
PIVOT
(
MAX(DateOfBirth)
FOR RowID
IN (' + @columns + ')
) AS PivotResult
ORDER BY ID'

EXEC(@sql)

I now want to compare columns in an outer query, but my usual structure does not work.

USE TEST
DECLARE @sql AS varchar(MAX)
DECLARE @columns AS varchar(MAX)

SELECT *
     ,CASE(.....
FROM
(
SELECT @columns = 
	COALESCE(@columns + ',','') + QUOTENAME(RowID)
FROM
(
SELECT DISTINCT RowID
FROM [dbo].[DateOfBirth]
) AS B
ORDER BY B.RowID

SET @sql = '
WITH PivotData AS
(
SELECT *
FROM [dbo].[DateOfBirth]
) 
SELECT
ID
,'+ @columns +'
FROM PivotData
PIVOT
(
MAX(DateOfBirth)
FOR RowID
IN (' + @columns + ')
) AS PivotResult
ORDER BY ID'

EXEC(@sql)
)
AS C

Any suggestions would be appreciated

You don't know what you don't know...