Q:

Use Results of CTE to Update Another Table

I have the following CTE that does what I want it to do. 
;WITH numbering AS 
(
    SELECT SrcID, AsOfDate, PID,
           dense_rank() OVER (PARTITION BY SrcID ORDER BY PID) AS rowno
    FROM   RAW_DATA
)
SELECT SrcID,
       MAX(CASE rowno WHEN 1 THEN PID END) AS PID1,
       MAX(CASE rowno WHEN 2 THEN PID END) AS PID2,
       MAX(CASE rowno WHEN 3 THEN PID END) AS PID3,
       MAX(CASE rowno WHEN 4 THEN PID END) AS PID4
FROM   numbering
GROUP BY SrcID

I need to use that, as well as the AsOfDate and PID, but I don’t want to display these in the CTE, as that throws off populating PID1, PID2, PID3, and PID4 . . .  all of which is correct now.   I do need the SrcID and AsOfDate to do an update to another table, named ‘RAW_DATA’.   How can I run the CTE to generate the specific data set that I need, and then do an update to the RAW_DATA table, based on joins between SrcID and AsOfDate? 
I think it should be something like this:
    ;WITH numbering AS 
    (
        SELECT SrcID, AsOfDate, PID,
               dense_rank() OVER (PARTITION BY SrcID ORDER BY PID) AS rowno
        FROM   RAW_DATA
    )
    SELECT SrcID,
           MAX(CASE rowno WHEN 1 THEN PID END) AS PID1,
           MAX(CASE rowno WHEN 2 THEN PID END) AS PID2,
           MAX(CASE rowno WHEN 3 THEN PID END) AS PID3,
           MAX(CASE rowno WHEN 4 THEN PID END) AS PID4
    FROM   numbering
    GROUP BY SrcID
    
INSERT INTO RAW_DATA(SrcID, AsOfDate, PID, PID1, PID2, PID3, PID4)
Select *
FROM         RAW_DATA INNER JOIN numbering 
             ON RAW_DATA.SrcID = numbering. SrcID 
             AND RAW_DATA.AsofDate = numbering. AsofDate

However, that throws this error: Invalid object name 'numbering'.   I am on SQL Server 2008. 
Update
Modifying my original post just a bit here. 
Jeffrey, I'm testing your solution:
--drop table Count_Unique_PID
;WITH numbering AS 
    (
        SELECT SrcID, AsOfDate, PID,PID1,PID2,PID3,PID4,
               dense_rank() OVER (PARTITION BY AsOfDate, SrcID ORDER BY PID) AS rowno
        FROM   RAW_DATA
    )

    SELECT SrcID,AsOfDate, PID,
           MAX(CASE rowno WHEN 1 THEN PID END) AS PID1,
           MAX(CASE rowno WHEN 2 THEN PID END) AS PID2,
           MAX(CASE rowno WHEN 3 THEN PID END) AS PID3,
           MAX(CASE rowno WHEN 4 THEN PID END) AS PID4
    INTO Count_Unique_PID
    FROM   numbering
    GROUP BY SrcID,AsOfDate, PID

    SELECT SrcID,
           AsOfDate,
           PID,
           PID1,
           PID2,
           PID3,
           PID4
    FROM   Count_Unique_PID
    GROUP BY SrcID,AsOfDate, PID,PID1,PID2,PID3,PID4

UPDATE    RAW_DATA
SET              PID1 = B.PID1, 
                 PID2 = B.PID2,
                 PID3 = B.PID3,
                 PID4 = B.PID4
FROM         RAW_DATA AS A INNER JOIN Count_Unique_PID  As B
             ON A.SrcID = B.SrcID 
             AND A.AsofDate = B.AsofDate

This runs, but it blows up my rows from 357,518 to 724,150.   The number of records should stay the same; it should remain 357,518 after the Update is done...something is still not quite right here.   Maybe I am missing a Group By somewhere, or something like that.   I don't see what the actual problem is.   Any additional thoughts on this? 

A:

CTEs can only be referenced by the immediately-following statement.  If you need the results later, you could insert the CTE into a temp table, then select from that temp table and then insert from that temp table:
;WITH numbering AS 
    (
        SELECT SrcID, AsOfDate, PID,
               dense_rank() OVER (PARTITION BY SrcID ORDER BY PID) AS rowno
        FROM   RAW_DATA
    )

    SELECT SrcID,
           MAX(CASE rowno WHEN 1 THEN PID END) AS PID1,
           MAX(CASE rowno WHEN 2 THEN PID END) AS PID2,
           MAX(CASE rowno WHEN 3 THEN PID END) AS PID3,
           MAX(CASE rowno WHEN 4 THEN PID END) AS PID4
    INTO #tmp
    FROM   numbering
    GROUP BY SrcID

    SELECT SrcID,
           PID1,
           PID2,
           PID3,
           PID4
    FROM   #tmp
    GROUP BY SrcID

INSERT INTO RAW_DATA(SrcID, AsOfDate, PID, PID1, PID2, PID3, PID4)
Select *
FROM         RAW_DATA INNER JOIN #tmp
             ON RAW_DATA.SrcID = #tmp. SrcID 
             AND RAW_DATA.AsofDate = #tmp. AsofDate

