SELECT statement with Dynamic Column Name

[kchandra25]

Hello,

I am trying to get a different COLUMN name(s) according to WHERE condition in a SQL SELECT statement. I am using SQL Server 2000.

For e:g

select [Name_A] from tblABC where ID = 5

If I am passing ID = 6 then I need to get the column Name Name_B.

According to ID, I need to select different Column and List all values.

Anybody Help me to prepare a dynamic SQL statement for this task.

Thanks in Advance,
CK.

[MAK]

--you can create a view as in the below example

use tempdb
Create table x1 (id int , [Name_A] varchar(10), [Name_B] varchar(10),
[Name_C] varchar(10), [Name_D] varchar(10))

insert into x1 select 1,'a','a','ae','a2'
insert into x1 select 4,'a1','la','fa','a2'
insert into x1 select 5,'a2','ak','ga','a3'
insert into x1 select 5,'a3','aj','ha','a4'
insert into x1 select 5,'a4','ha','ja','a4'
insert into x1 select 7,'a5','ga','ka','a5'
insert into x1 select 6,'a6','af','la','a6'
insert into x1 select 6,'a7','da',';a','a6'

Create view x1_view as
Select ID, ColumnX = case ID when 4 then [Name_A]
when 5 then [Name_B]
when 6 then [Name_C]
when 7 then [Name_D]
else NULL end
from x1

select * from x1_view where id =5
--results
5 ak
5 aj
5 ha


select * from x1_view where id =6

--results
6 la
6 ;a

[MAK]

or a simple select statement like below.


declare @id int
set @id =6
Select ID, ColumnX = case ID when 4 then [Name_A]
when 5 then [Name_B]
when 6 then [Name_C]
when 7 then [Name_D]
else NULL end
from x1 where id=@id