different 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

SQL Server 2005 Forums

Transact-SQL (2005)

different column name

Author

Topic

cimon
Starting Member

4 Posts

Posted - 2009-06-01 : 12:34:15

I have some table like this
IDTABLE

ID OTHERCOLUMNS
SKIN Othercolumn value1
LUNGS Othercolumn value2
KIDNEYS Othercolumn value3
EYES Othercolumn value4

I want the result as....if ID is skin select it under column ExternalID and if ID is Lungs select it under column heading internal id .

Like this

ExternalID InternalID
SKIN LUNGS

so for this I did this

SELECT ExternalID = CASE WHEN ID ='SKIN' THEN ID
END,
InternalID = CASE WHEN ID ='LUNGS' THEN ID
END
FROM IDTAble
WHERE ID IN (SKIN','LUNGS')

This gives me

ExternalID InternalID
SKIN NULL
NULL LUNGS

How do I get result as

ExternalID InternalID
SKIN LUNGS

How do I select only non null values

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-01 : 13:00:30

[code]SELECT ExternalID = MAX(CASE WHEN ID ='SKIN' THEN ID ELSE NULL END),TBCompliantID = MAX(CASE WHEN ID ='LUNGS' THEN ID ELSE NULL END)
FROM IDTAble
WHERE ID IN (SKIN','LUNGS')
[/code]

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-01 : 13:43:20

Please don't edit your first post!
We cannot see what has changed and the replies maybe then look wrong!

No, you're never too old to Yak'n'Roll if you're too young to die.

cimon
Starting Member

4 Posts

Posted - 2009-06-01 : 15:24:17

Thank you
this works

quote:

Originally posted by visakh16

SELECT ExternalID = MAX(CASE WHEN ID ='SKIN' THEN ID ELSE NULL END),TBCompliantID = MAX(CASE WHEN ID ='LUNGS' THEN ID ELSE NULL END)
FROM IDTAble
WHERE ID IN (SKIN','LUNGS')

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-02 : 12:39:58

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources