Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Use a variable as a column name - without dynamic SQL? Expand / Collapse
Author
Message
Posted Saturday, August 25, 2012 7:02 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 1:33 PM
Points: 191, Visits: 412
CREATE TABLE #tblItems
(
ItemID int,
ItemDescription varchar(50),
ItemDate smalldatetime
);

INSERT INTO #tblItems
(ItemID, ItemDescription, ItemDate)
SELECT 1, 'Item 1', '01 Jan 2012' UNION ALL
SELECT 2, 'Item 2', '03 Jan 2012' UNION ALL
SELECT 3, 'Item 10', '14 Jan 2012';

SELECT ItemID AS [Item ID] FROM #tblItems

DECLARE @SQLstring nvarchar(500)
DECLARE @ColName varchar(100)
SET @ColName = 'Order ID'
SET @SQLstring = 'SELECT ItemID AS [' + @ColName + '] FROM #tblItems'
EXEC sp_executesql @SQLstring

DROP Table #tblItems

I have a column which for some clients might contain an Order Number. For another client it might contain a Job No etc.

Is it possible to run a SELECT statement in such a way that I can return a Column Name for that column that makes sense to the person viewing it. In the example above the first column is called ItemID. But for Company A I might want to do this:

Select ItemID AS [Order ID] ...

whereas for Company B I might want to do this:

Select ItemID AS [Job Number] ...

Is there a way of doing this without using dynamic SQL?
Post #1350037
Posted Saturday, August 25, 2012 12:51 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 12:41 PM
Points: 31,177, Visits: 15,618
Not really. You can use a CASE type expressions as
...
If @item = 'Job'
then
select
JobID = OrderID
from #TblItems
else if @item = 'Order'
select OrderID = OrderID
from #tblItems
...









Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1350059
Posted Saturday, August 25, 2012 12:59 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 9:14 AM
Points: 9,294, Visits: 9,483
No, you cannot generally variablize column names, Dynamic SQL is how to do this.

There are some limited ways around it for specific cases, using if statements or other high-level branch techniques to select different queries based on the variable, but they're more trouble than they're worth once it goes beyond more than a couple of choices.

If you do use Dynamic SQL for this, however, you need to make sure that it's protected against SQL Injection.


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #1350064
Posted Monday, August 27, 2012 3:49 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 1:33 PM
Points: 191, Visits: 412
Thank you both for your help.
Post #1350287
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse