How to display column_names based on condition

Question

Post reply

How to display column_names based on condition

Minnu

SSCertifiable

Points: 6451
More actions
January 8, 2013 at 2:25 am

#268931

Hi Sql Server Team,
HAPPY NEW YEAR.
--
Am having below table
Table1
Col_1Col2Col3
----------------------------------------
100ProdExt
101SalesExd
102MarkTet
103PurcKet
i want to query to display the column_names
Eg_1: if table1.col_1 contains 100 then output should be Col_1
Eg_2: if table1.col_3 contains Tet then output should be Col_3
based on my condtion column_name should be displayed as output.
Please help

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Aadhar Joshi Hall of Fame Points: 3202 More actions · Answer 1

Aadhar Joshi

Hall of Fame

Points: 3202

January 8, 2013 at 2:51 am

#1574689

Best way is to use union for all condition..

Minnu SSCertifiable Points: 6451 More actions · Answer 2

Minnu

SSCertifiable

Points: 6451

January 8, 2013 at 4:38 am

#1574745

Query Please....

PiMané SSChampion Points: 13965 More actions · Answer 3

think this is what you want....

create table t1 (col1 varchar(10), col2 varchar(10), col3 varchar(10))

insert into t1 values ('100', 'Prod', 'Ext'), ('101', 'Sales', 'Exd'), ('102', 'Mark', 'Tet'), ('103', 'Purc', 'Ket')

SELECT

CASE

WHEN col1 = '100' THEN 'col1'

ELSE col1

END col1,

col2,

CASE

WHEN col3 = 'Tet' THEN 'col3'

ELSE col3

END col3

FROM t1

Pedro

If you need to work better, try working less...

Minnu SSCertifiable Points: 6451 More actions · Answer 4

Sorry,,,

Small change in my requeirement...

Table_Name : Table1

Col_1Col_2

----------------------

100Prod

101Sales

102Mark

103Purc

if exists (select 1 from Table1 where Col_1=100) then

i want to get the corresponding column value of 100, that is "Prod" as output.

--

Query please...

PiMané SSChampion Points: 13965 More actions · Answer 5

Minnu (1/8/2013)
Sorry,,,
Small change in my requeirement...
Table_Name : Table1
Col_1Col_2
----------------------
100Prod
101Sales
102Mark
103Purc
if exists (select 1 from Table1 where Col_1=100) then
i want to get the corresponding column value of 100, that is "Prod" as output.
--
Query please...

Can you post the query result?! I don't quite understand what you want..

Pedro

If you need to work better, try working less...

Minnu SSCertifiable Points: 6451 More actions · Answer 6

My Requirement is :

I have to check whether col_1 contains 100, if Yes then insert corresponding value (i.e Prod)

into another table.

like that i've to search for multiple records.

PiMané SSChampion Points: 13965 More actions · Answer 7

Minnu (1/8/2013)
My Requirement is :
I have to check whether col_1 contains 100, if Yes then insert corresponding value (i.e Prod)
into another table.
like that i've to search for multiple records.

Then a simple:

INSERT INTO tabledestination SELECT WhatEver FROM table1 WHERE Col1 = 100

will do the trick...

Just insert in you destination table there records that have 100 on col1 from table1.

Pedro

If you need to work better, try working less...

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

Minnu (1/8/2013)
Sorry,,,
Small change in my requeirement...
Table_Name : Table1
Col_1Col_2
----------------------
100Prod
101Sales
102Mark
103Purc
if exists (select 1 from Table1 where Col_1=100) then
i want to get the corresponding column value of 100, that is "Prod" as output.
--
Query please...

SELECT Col2

FROM mytable

WHERE Col1 = 100

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden