How to pass columns alias names from variables

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

SQL

Declare @year1 varchar(10)='1998'
Declare @year2 varchar(10)='1999'

select
     sum(case when year=@year1 then sales end) '1998'--@year1
    ,sum(case when year=@year2 then sales end) '1999'--@year2
from salestable

In the above query instead of giving static column names i wanna pass the variable value as a column alias name in case.
If i pass 2004 for variable means in want to get 2004 as a column name...

Posted 16-Sep-13 23:34pm

Raju9959

Updated 16-Sep-13 23:36pm

v2

Add a Solution

Comments

Abdul Quader Mamun 17-Sep-13 5:37am

You need to use dynamic SQL.

3 solutions

Solution 3

To convert rows as columns, use PIVOT[^], instead doing it with CASE WHEN ... END statement.

SQL

DECLARE @cols VARCHAR(300)
DECLARE @dt VARCHAR(2000)
DECLARE @pt VARCHAR(4000)

SET @cols = '[1998],[1999],[2000]'

SET @dt = 'SELECT ProductID, YEAR(SalesDate) AS [Year], Sales FROM ProductSales'

SET @pt = 'SELECT ProductID, ' + @cols + ' ' +
          'FROM (' + @dt + ') AS DT ' +
          'PIVOT(SUM(Sales) FOR [Year] IN(' + @cols + ')) AS PT'
EXEC(@pt)

For further information, please see:
http://blog.sqlauthority.com/2008/06/07/sql-server-pivot-and-unpivot-table-examples/[^]
Simple Way To Use Pivot In SQL Query[^]
How to Perform Pivot Operation in T-SQL..?[^]

Posted 17-Sep-13 1:57am

Maciej Los

Updated 17-Sep-13 2:02am

v2

Comments

Raja Sekhar S 18-Sep-13 0:17am

Nice.. Forgot about Pivot... +5!

Maciej Los 18-Sep-13 14:56pm

Thank you ;)

Solution 1

try

sum(case when year=@year1 then sales as @year1 end)

Posted 16-Sep-13 23:55pm

Member 8622273

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Raja Sekhar S · Accepted Answer · 2013-09-17T00:11:00

Solution 2

SQL

Declare @year1 varchar(10)='1998'
Declare @year2 varchar(10)='1999', @Sql nvarchar(200)

Set @Sql ='Select sum(case when year=' + @year1 +' then sales end) [' + @year1 +'],sum(case when year=' + @year2 + ' then sales end) [' + @year2 +'] from salestable'

-- Sql Data will be
-- Select sum(case when year=1998 then sales end) [1998],
--        sum(case when year=1999 then sales end) [1999] 
-- from salestable

Exec (@Sql)

Posted 17-Sep-13 0:11am

Raja Sekhar S

Updated 17-Sep-13 1:29am

v4

Comments

Raju9959 17-Sep-13 7:26am

is it any possibility to get such thing without dynamic statements

Raja Sekhar S 17-Sep-13 7:31am

I think it's not possible.... any one correct me if am wrong...
is there any reason for you to not use dynamic Query..?

Raju9959 17-Sep-13 7:37am

Not like that i want to know the alternative method if available...

Raja Sekhar S 17-Sep-13 7:42am

No other way as of i know... if it answered your question mark it as answer...

Maciej Los 17-Sep-13 7:58am

See my answer.