How to get the column name - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to get the column name

Author

Topic

Naveen Merupula
Starting Member

6 Posts

Posted - 2011-04-26 : 03:38:51

Hi,

I want to retrieve the name of the Column based on the values in the corresponding rows.

For example,

CustID , Custname1 , custname2
1 XYZ ABC
2 xxx XYZ

Now I wantthe Column name which is having XYZ in the rows of CustID 1 and 2

That is I want the output as Below

CustID Columnname
1 Custname1
2 Custname2

Be happy.. :)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-04-26 : 03:57:07

[code]
select CustID, ColumnName = case when Custname1 = 'XYZ' then 'Columnname1' when Custname2 = 'XYZ' then 'Columnname2' end
from YourTable
[/code]

KH
[spoiler]Time is always against us[/spoiler]

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2011-04-26 : 05:05:11

Little correction as per OP

quote:
Originally posted by khtan

select CustID, ColumnName = case when Custname1 = 'XYZ' then 'Custname1' when Custname2 = 'XYZ' then 'Custname2' end
from YourTable

KH
[spoiler]Time is always against us[/spoiler]

Vaibhav T

If I cant go back, I want to go fast...

Naveen Merupula
Starting Member

6 Posts

Posted - 2011-04-26 : 05:19:44

Thanks....will try it.

Be happy.. :)

Naveen Merupula
Starting Member

6 Posts

Posted - 2011-04-26 : 05:27:54

hi
If my table is like this:
CustID , Custname1 , custname2
1 XYZ ABC
2 xxx XYZ
3 XXX XXX

After executing the query....i am getting the output like this
CustID Columnname
1 Custname1
2 Custname2
3 Null

But I dont want Null value record in the output...
can u suggest me the solutin for this

Be happy.. :)

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-04-26 : 05:56:56

Just put this condition after Katan querey
where Columnname is not null

Like this
select CustID, ColumnName = case when Custname1 = 'XYZ' then 'Custname1' when Custname2 = 'XYZ' then 'Custname2' end
from YourTable
where Columnname is not null

Raghu' S

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-04-26 : 06:00:44

the WHERE clause should be


WHERE Custname1 = 'XYZ'
or    Custname2 = 'XYZ'

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-04-26 : 06:01:45

quote:
Originally posted by raghuveer125

Just put this condition after Katan querey
where Columnname is not null

Like this
select CustID, ColumnName = case when Custname1 = 'XYZ' then 'Custname1' when Custname2 = 'XYZ' then 'Custname2' end
from YourTable
where Columnname is not null

Raghu' S

You can't reference a column alias in the WHERE clause like that.

However, using a derived table or CTE, it will be possible


select *
from
(
    select CustID, ColumnName = case when Custname1 = 'XYZ' then 'Custname1' when Custname2 = 'XYZ' then 'Custname2' end
    from YourTable
) t
where ColumnName is not null

KH
[spoiler]Time is always against us[/spoiler]

Naveen Merupula
Starting Member

6 Posts

Posted - 2011-04-26 : 06:18:25

Ya I got it....:)

Thanks

Be happy.. :)

Naveen Merupula
Starting Member

6 Posts

Posted - 2011-04-26 : 08:25:50

Hi,

I have the following table
MEMBER_ID PROV_ID FLAG
110 PCP001 F
110 NPR001 R
111 PCP001 F
111 PCP001 R
112 NPR001 F
112 NPR001 R
113 NPR001 F
113 PCP001 F
113 NPR001 R

Now I want to update the Flag column as per the below conditions:
1.Whenever the PROV_ID field is same and Flag value is 'F' and 'R' then that Flag value for the meber should be changed to 'B'

I want the result as follows :

MEMBER_ID PROV_ID FLAG
110 PCP001 F
110 NPR001 R
111 PCP001 B
112 NPR001 B
113 NPR001 F
113 PCP001 F
113 NPR001 R

can u please provide me the solution

Be happy.. :)

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2011-04-26 : 08:43:18

Post this as a different question as its totally irrelevant to the current post...

Vaibhav T

If I cant go back, I want to go fast...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-04-26 : 08:44:25

[code]
; with
tbl as
(
select MEMBER_ID, PROV_ID = max(PROV_ID), FLAG = 'B'
from yourtable
group by MEMBER_ID
having min(PROV_ID) = max(PROV_ID)
and min(FLAG) = 'F'
and max(FLAG) = 'R'
)
select MEMBER_ID, PROV_ID, FLAG
from yourtable t
where not exists (select * from tbl x where x.MEMBER_ID = t.MEMBER_ID)
union all
select MEMBER_ID, PROV_ID, FLAG
from tbl
order by MEMBER_ID
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Naveen Merupula
Starting Member

6 Posts

Posted - 2011-04-26 : 08:59:27

Will u please explain how can we use aggregate functions like min() and max() on datatypes like varchar...and usage of min(prov_id) and min(flag) and max(flag) in the above solution

Be happy.. :)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-04-26 : 09:49:24

min() and max() also works on string not only on numbers. It will be based on the ascii code of the character

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources