I am trying to name my columns from the results of a Select query using date information and variables but I am probably expecting to much from my simple query. My Table HstTotals is laid out as below:

HstTotals:

DOB        FKid   Sales
----       ----   -----
2024-05-01  600     0
2024-05-01  601     1257.89
2024-05-01  602     567.22
2024-05-02  603     238.49

What I need in the following:

FKid    2024-05-01  2024-05-02
----    ----------  ----------
601      1257.89          0
602       567.22          0
603            0     238.49

Below is the query I am using:

Declare @CMP VARCHAR(MAX)
Set @CMP = Convert(varchar(12),dateadd(day,-1,getdate()),23)


SELECT 
     [FKid]
    ,Sum ([Sales]) as @CMP
 

FROM [Replication].[dbo].[HstTotals]
where DOB between (DATEADD(DAY, -8, GETDATE())) and (DATEADD(DAY, -1, GETDATE()))
and FKid > '600'

group by [FKid]

My query gives me the following error: Msg 102, Level 15, State 1, Line 7
Incorrect syntax near ‘@CMP’.

In doing some research I’m not sure that I can pass a variable result along as a column name but to be honest I’m not sure if I am even starting correctly.

I would appreciate your input.