T-SQL Dynamic alias without using dynamic SQL

2019-08-17 03:18发布

问题:

I need the column alias to be named based on a scenario

declare @testing as varchar(max)
set @testing = 'choice'

select 1 as case when @testing = 'choice' then 'chose' else 'didntChoose' end

So if @testing = 'choice', the results would look like this:

chose
1

else:

didntChoose
1

Is it possible to do this without dynamic SQL?

回答1:

No, you cannot change the name of the alias based on the value unless you use dynamic SQL.

When you are selecting the columns, you can only have one name/alias for each column.

If you want different column names, then you could use some like the following which uses different select statements:

IF @testing = 'choice'
    select 1 as 'Chose'
ELSE 
    select 1 as 'didntChoose'   

Or you could return two separate columns:

select 
    case when @testing = 'choice' then 1 else 0 end Chose,
    case when @testing <> 'choice' then 1 else 0 end DidNotChose


回答2:

Here is something I wrote that kind of achieves the goal, but it is not the most elegant piece of work I have ever done.

Various customers want to display different values for attributes associated with their resources. So a generic table exists that lets each customer assign different values to each of the resources.

Let's create the structures first and populate them. Nothing too fancy:

create table CustResource (
CustId int,
Attr1 varchar(50),
Attr2 varchar(50),
Attr3 varchar(50),
Attr4 varchar(50),
Attr5 varchar(50))

insert into CustResource (CustId, attr1, attr2, attr3, attr4) values (1, 'Div','Dept','Machine Type','Main Usage')

/* What just happened above is that the customer assigned display values to the first 4 attributes only */

create table PortalResource (
ResourceId int,
custId     int,
ResourceName varchar(50),
Attr1 varchar(50),
Attr2 varchar(50),
Attr3 varchar(50),
Attr4 varchar(50),
Attr5 varchar(50))


insert into PortalResource (ResourceId, CustId, ResourceName, attr1, attr2, attr3, attr4) 
   values (10,1,'abcd1234','Local Government','State Emergency Services','File Server','Production')
insert into PortalResource (ResourceId, CustId, ResourceName, attr1, attr2, attr3, attr4) 
   values (11,1,'bcde2345','Local Government','State Emergency Services','Database Server','Production')
insert into PortalResource (ResourceId, CustId, ResourceName, attr1, attr2, attr3, attr4) 
   values (12,1,'bcde2346','Local Government','Department of Education','Domain Controller','Production')

/* Notice in the above that attr5 is not populated. This is deliberate! */

/* OK, now we want to accept the customer Id (I have hard-coded it here for quick reference, but you get the point) */

declare @SQLString varchar(1000)
    , @attr1 varchar (50)
    , @attr2 varchar(50)
    , @attr3 varchar(50)
    , @attr4 varchar(50)
    , @attr5 varchar(50)
    , @CustId varchar(10)

set @CustId = 1
select @attr1 = upper(attr1)
     , @attr2 = upper(attr2)
     , @attr3 = upper(attr3)
     , @attr4 = upper(attr4 )
     , @attr5 = UPPER(attr5)
     , @CustId = convert(varchar,custId)
from CustResource where custid = @CustId

set @SQLString = 'Select '  + @CustId + 'as CustomerID'
If @attr1  is not null set @SQLString = @SQLString  + 
              ' , attr1 as '   + '"' + @attr1 + '"' 
If @attr2  is not null set @SQLString = @SQLString  + 
              ' , attr2 as ' + '"' + @attr2 + '"' 
If @attr3  is not null set @SQLString = @SQLString  + 
              ' , attr3 as ' + '"' + @attr3 + '"' 
If @attr4  is not null set @SQLString = @SQLString  + 
              ' , attr4 as ' + '"' + @attr4 + '"' 
If @attr5  is not null set @SQLString = @SQLString  + 
              ' , attr5 as ' + '"' + @attr5 + '"' 
Set @SQLString = @SQLString + ' from PortalResource where CustId = ' + @CustId 

print @SQLString

exec (@SQLString)

This works a charm, but it is super-ugleeeeee!!!!



回答3:

I'll just leave this here http://www.sommarskog.se/dynamic_sql.html#columnalias

You first get the data into a temp table, and then you use sp_rename to rename the column along your needs. (You need to qualify sp_rename with tempdb to have it to operate in that database.)

Do have a read of his site if you're dealing a lot with dynamic SQL, there's a lot of ways to shoot yourself in the foot if you're not careful...