August 25, 2012 at 7:02 am
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?
August 25, 2012 at 12:51 pm
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
...
August 25, 2012 at 12:59 pm
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.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 27, 2012 at 3:49 am
Thank you both for your help.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply