SQL SERVER 2008 - Use a Variable as a Column Name

Hubbsjp21
Hubbsjp21 used Ask the Experts™
on
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

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
you would have to use dyanmic SQL, see below, only thing is anywhere you use a single quote in the query you have to replace it with 2 single quotes except around the variable.


EXEC('
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 ' + @strLSTEOM + ',
   
FROM ScoreCard1
WHERE STATUS NOT IN(''CLOSED'', ''REO'')
--GROUP BY STATUS
GROUP BY STATUS with CUBE

')
Commented:
To do that you need to use dynamic SQL.  Something like this:



DECLARE @strLASTEOM AS VARCHAR(10)
SET @strLASTEOM = '6/30/2010'
DECLARE @LASTEOM AS DATETIME
SET @LASTEOM = CAST(@strLASTEOM AS DATETIME)

DECLARE @strSQL as VARCHAR(4000)
SET @strSQL = 'SELECT STATUS '

SET @strSQL = @strSQL +
', SUM(CASE AS_OF_DT WHEN DATEADD(d,-1,DATEADD(mm, DATEDIFF(m,0,CONVERT(DATETIME,''' + @strLASTEOM + ''',101))-4,0)) THEN UPB ELSE 0 END) AS [' + CONVERT(VARCHAR(10),DATEADD(d,-1,DATEADD(mm, DATEDIFF(m,0,@LASTEOM)-4,0)) ,101) + ']'
SET @strSQL = @strSQL +
', SUM(CASE AS_OF_DT WHEN DATEADD(d,-1,DATEADD(mm, DATEDIFF(m,0,CONVERT(DATETIME,''' + @strLASTEOM + ''',101))-3,0)) THEN UPB ELSE 0 END) AS [' + CONVERT(VARCHAR(10),DATEADD(d,-1,DATEADD(mm, DATEDIFF(m,0,@LASTEOM)-3,0)) ,101) + ']'
...

SET @strSQL = @strSQL +
' FROM ScoreCard1
WHERE STATUS NOT IN(''CLOSED'', ''REO'') 
GROUP BY STATUS '

--SELECT @strSQL  --this is for testing

EXEC (@strSQL)

Open in new window

Author

Commented:
dgmg - your code worked perfectly!  I awarded 450 to dgmg, and 50 to Brad.  Brad, although your concept was the same as dgmg's and you were first, I got errors when I tried to run it,  I tried to make corrections to the errors, but to no avail.  Dgmgs worked right out of the gate.

Thanks to both of you for the time - Hubbs

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial