CODEWITHSUNDEEP
sql-servert-sql
blubr
blubr

Reputation: 122

How do I title columns as a variable in SQL?

new to SQL and need some help. I'm trying to use a variable as a column title and iterate sums over a certain range that changes. Here's the code below.

DECLARE @startmonth AS INT
DECLARE @endmonth AS INT
DECLARE @earnedpremiumyear AS INT
SET @startmonth = 200501
SET @endmonth = 200512
SET @earnedpremiumyear = 2005

WHILE @startmonth <= 201301
BEGIN
    SELECT SUM(earnedpremium) AS @earnedpremiumyear
    FROM dbo.Database
    WHERE accountingmonth BETWEEN @startmonth AND @endmonth
    AND earnedendingmonth BETWEEN @startmonth AND @endmonth
    SET @startmonth = @startmonth + 100
    SET @endmonth = @endmonth + 100
    SET @earnedpremiumyear = @earnedpremiumyear + 1
END

I want it so that the title of the SUM is the earned premium year, starting at 2005 and going up to 2013. When I run this code, I get this error:

Incorrect syntax near '@earnedpremiumyear'

so I'm definitely calling the variable wrong. Can anyone help?

Also, how do I make it so that instead of creating a table for each year total, the sums are all on one table in one column or row?

Thanks!

Upvotes: 3

Views: 899

Answers (3)

Pரதீப்
Pரதீப்

Reputation: 93694

Some thing lyk this..

DECLARE @f   VARCHAR(10)='fff',
        @sql NVARCHAR(max)

SET @sql ='select 1 as ' + @f 

EXEC Sp_executesql
  @sql

Upvotes: 0

Bensonius
Bensonius

Reputation: 1541

The only way I can think of doing it is through writing a dynamic SQL query:

DECLARE @startmonth AS INT
DECLARE @endmonth AS INT
DECLARE @earnedpremiumyear AS INT
SET @startmonth = 200501
SET @endmonth = 200512
SET @earnedpremiumyear = 2005
DECLARE @sql VARCHAR(MAX) = '';


WHILE @startmonth <= 201301
    BEGIN
    SELECT @sql = 'SUM(earnedpremium) AS ' + @earnedpremiumyear
    + 'FROM dbo.Database
    WHERE accountingmonth BETWEEN' + @startmonth + ' AND ' + @endmonth
    ...
    EXEC(@sql)
END

That can get pretty ugly though.

Upvotes: 0

AHiggins
AHiggins

Reputation: 7219

Can you try simplifying it to the following?

SELECT 
    LEFT(CAST(AccountingMonth AS VARCHAR(4)),4) AS AccountingYear,
    SUM(EarnedPremium) AS TotalEarnedPremium
FROM Database.dbo.Table 
WHERE 
    AccountingMonth BETWEEN 200501 AND 201301 AND 
    EarnedEndingMonth BETWEEN 200501 AND 201301 
GROUP BY LEFT(CAST(AccountingMonth AS VARCHAR(4)),4)

Upvotes: 4

Related Questions