Avatar of ALawrence007
ALawrence007 asked on

How to format Column Header

Hi,

I hope that this is possible. My query below generates a total for a month in my SQL database. As you can see the Column header is hard coded as [Feb Total] at the moment. Can I make that Dynamic so that if the month changes the column header changes as well. It does not HAVE to be the month name, but could also be '02/01/2008' if needs be.

Thanks
Declare @Month DateTime  
Set @Month = '02/01/2008'  
SELECT     SUM(CASE WHEN ([Transaction].Time BETWEEN DATEADD(month, DATEDIFF(month, 0, @Month), 0) AND DATEADD(month, DATEDIFF(month, 0, @Month) + 1, 0)) 
THEN [Transaction].Total ELSE 0 END) AS [Feb Total]
FROM         [Transaction] INNER JOIN
TransactionEntry ON [Transaction].TransactionNumber = TransactionEntry.TransactionNumber
WHERE     ([Transaction].Time BETWEEN DATEADD(month, DATEDIFF(month, 0, @Month), 0) AND DATEADD(month, DATEDIFF(month, 0, @Month) + 1, 0))

Open in new window

Microsoft SQL ServerMicrosoft SQL Server 2005SQL

Avatar of undefined
Last Comment
ALawrence007

8/22/2022 - Mon
dportas

If there is only one month then why would you care about the column name? Have you conisdered using PIVOT?
SOLUTION
matrix_aash

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
ALawrence007

That is just it, I am going to add all 12 months, but still had to code that.
Declare @Month DateTime  
Set @Month = '02/01/2008'  
SELECT     SUM(CASE WHEN ([Transaction].Time BETWEEN DATEADD(month, DATEDIFF(month, 0, @Month), 0) 
			AND DATEADD(month, DATEDIFF(month, 0, @Month) + 1, 0)) 
			THEN [Transaction].Total ELSE 0 END) AS [Feb Total],
SUM(CASE WHEN ([Transaction].Time BETWEEN DATEADD(month, DATEDIFF(month, - 1, @Month), 0) 
			AND DATEADD(month, DATEDIFF(month, -1, @Month) + 1, 0)) 
			THEN [Transaction].Total ELSE 0 END) As [Jan Total],
SUM(CASE WHEN ([Transaction].Time BETWEEN DATEADD(month, DATEDIFF(month, - 2, @Month), 0) 
			AND DATEADD(month, DATEDIFF(month, - 2, @Month) + 1, 0)) 
			THEN [Transaction].Total ELSE 0 END) As [Dec Total],
SUM(CASE WHEN ([Transaction].Time BETWEEN DATEADD(month, DATEDIFF(month, - 3, @Month), 0) 
			AND DATEADD(month, DATEDIFF(month, - 3, @Month) + 1, 0)) 
			THEN [Transaction].Total ELSE 0 END) As [NOV Total]
FROM         [Transaction] INNER JOIN
                      TransactionEntry ON [Transaction].TransactionNumber = TransactionEntry.TransactionNumber
WHERE     ([Transaction].Time BETWEEN DATEADD(month, DATEDIFF(month, 0, @Month), 0) 
			AND DATEADD(month, DATEDIFF(month, 0, @Month) + 1, 0)) OR
			([Transaction].Time BETWEEN DATEADD(month, DATEDIFF(month, - 1, @Month), 0) 
			AND DATEADD(month, DATEDIFF(month, -1, @Month) + 1, 0)) OR
			([Transaction].Time BETWEEN DATEADD(month, DATEDIFF(month, - 2, @Month), 0) 
			AND DATEADD(month, DATEDIFF(month, - 2, @Month) + 1, 0)) OR
			([Transaction].Time BETWEEN DATEADD(month, DATEDIFF(month, - 3, @Month), 0) 
			AND DATEADD(month, DATEDIFF(month, - 3, @Month) + 1, 0))

Open in new window

ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
matrix_aash

You can declare it as a variable. @columnname can be anything whatever you want.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ASKER
ALawrence007

YZlat,

Your answer I got working, but the following way:

If I do it the way you suggested I keep on getting an error: Must Declare @Month

Declare @Month DateTime 
Declare @sqlstr varchar(1000)
Set @Month = '02/01/2008'  
 
Set @sqlstr = 'Declare @Month DateTime 
Set @Month = ''02/01/2008''SELECT     SUM(CASE WHEN ([Transaction].Time BETWEEN DATEADD(month, DATEDIFF(month, 0, @Month), 0) AND DATEADD(month, DATEDIFF(month, 0, @Month) + 1, 0)) 
THEN [Transaction].Total ELSE 0 END) AS [' + datename(mm, @Month) + ' Total]
FROM         [Transaction] INNER JOIN
TransactionEntry ON [Transaction].TransactionNumber = TransactionEntry.TransactionNumber
WHERE     ([Transaction].Time BETWEEN DATEADD(month, DATEDIFF(month, 0, @Month), 0) AND DATEADD(month, DATEDIFF(month, 0, @Month) + 1, 0))'
 
exec(@sqlstr)

Open in new window

Patrick Matthews

ALawrence007,

Did you try my suggestion?

Regards,

Patrick
ASKER
ALawrence007

matthewspatrick,
I did,

I get the following error:

Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '.01'.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
ALawrence007

I managed to get all to work, but am using YZlat's.

Thanks