SQL SERVER 2008 - Use a Variable as a Column Name

Hello Experts.

I am a newbie in SQL, and just learned how to create a "Pivot Table" using Transact SQL.  I want to use a variable value as a column name in the table, but get an error no matter how I try to do it.  I am not even sure it is possible, but need to try.

In the attached code, line 12, I think you can see that I am trying to name the column as the value for @strLSTEOM, which is a date converted to a string (if I even did that right) = 6/30/2010.  Once I learn how to do this, I will name the other columns accordingly.  How can I make this work?

Thanks - Hubbs

DECLARE @LASTEOM AS DATE
SET @LASTEOM = '6/30/2010'
DECLARE @strLSTEOM As Varchar
SET @strLSTEOM = CONVERT(VARCHAR, @LASTEOM)

SELECT STATUS, 
    SUM(CASE AS_OF_DT WHEN DATEADD(d,-1,DATEADD(mm, DATEDIFF(m,0,@LASTEOM)-4,0)) THEN UPB ELSE 0 END) AS EOM_6, -- want it to be @strLSTEOM,
    SUM(CASE AS_OF_DT WHEN DATEADD(d,-1,DATEADD(mm, DATEDIFF(m,0,@LASTEOM)-3,0)) THEN UPB ELSE 0 END) AS EOM_5,
    SUM(CASE AS_OF_DT WHEN DATEADD(d,-1,DATEADD(mm, DATEDIFF(m,0,@LASTEOM)-2,0)) THEN UPB ELSE 0 END) AS EOM_4,
    SUM(CASE AS_OF_DT WHEN DATEADD(d,-1,DATEADD(mm, DATEDIFF(m,0,@LASTEOM)-1,0)) THEN UPB ELSE 0 END) AS EOM_3,
    SUM(CASE AS_OF_DT WHEN DATEADD(d,-1,DATEADD(mm, DATEDIFF(m,0,@LASTEOM),0)) THEN UPB ELSE 0 END) AS EOM_2, -- want it to be @strLSTEOM,
    SUM(CASE AS_OF_DT WHEN DATEADD(d,-1,DATEADD(mm, DATEDIFF(m,0,@LASTEOM)+1,0)) THEN UPB ELSE 0 END) AS @strLSTEOM, -- want it to be @strLSTEOM
    SUM(CASE AS_OF_DT WHEN DATEADD(d,-1,DATEADD(mm, DATEDIFF(m,0,@LASTEOM)-11,0)) THEN UPB ELSE 0 END) AS Prior_Year -- want it to be @strLSTEOM,
    
FROM ScoreCard1
WHERE STATUS NOT IN('CLOSED', 'REO') 
--GROUP BY STATUS 
GROUP BY STATUS with CUBE

Select allOpen in new window