SQL Statement Help

Question

Post reply

SQL Statement Help

rs

SSCrazy

Points: 2819
More actions
November 17, 2004 at 12:24 pm

#60442

I got the following data in a table
Col1 Col2
HHHH TTTTT
HHHH RRRRR
HHHH SSSSS
AAAA FFFFF
BBBB GGGGG
CCCC HHHHH
DDDD IIIII
I want the follwing result set
TTTTT
RRRRR
SSSSS
AAAA
BBBB
CCCC
DDDD
Means where ever i see HHHH in Col1 i have to select Col2, Can you help on this.
Thanks in Advance

Viewing 12 posts - 1 through 11 (of 11 total)

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

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 1

How bout

CASE Col1 = 'HHHH' THEN Col2 ELSE Col1 END AS SReturn ???

You may want to review COALESCE as well..

Good Hunting!

AJ Ahrens

webmaster@kritter.net

rs SSCrazy Points: 2819 More actions · Answer 2

rs

SSCrazy

Points: 2819

November 17, 2004 at 12:41 pm

#530541

Thanks!, It worked

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

How would this look like with COALESCE?

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 4

I honestly don't know Frank. I know that I generally forget about COALESCE until I read about it here. Figured the CASE and COALESCE in certain instances go together.

Good Hunting!

AJ Ahrens

webmaster@kritter.net

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

Hey, I wouldn't use COALESCE unless I really need the extended functionality compared to ISNULL or have to care about portability. Why? Have a play with this snippets.

CREATE TABLE #t

(

c1 CHAR

)

INSERT INTO #t VALUES (NULL)

SELECT

ISNULL(c1,'Frank')

, COALESCE(c1,'Frank')

FROM

#t

SELECT ISNULL(c1,'Frank')

, COALESCE(c1,'Frank')

,CASE WHEN c1 IS NULL THEN 'Frank' ELSE c1 END

FROM

#t

DROP TABLE #t

SELECT

7 / ISNULL(CAST(NULL AS int), 2.00)

, 7 / COALESCE(CAST(NULL AS int), 2.00)

, 7 / CASE WHEN CAST(NULL AS int) IS NULL THEN 2.00 END

Now, run this and then read the explanations for both commands WORD by WORD in BOL

The following is by Umachandar Jayachandran from the MS newsgroups. Compare the execution plans

select

coalesce((

select

a2.au_id

from

pubs..authors a2

where

a2.au_id = a1.au_id ),'')

from

pubs..authors a1

select

isnull((

select

a2.au_id

from

pubs..authors a2

where

a2.au_id = a1.au_id ),'')

from

pubs..authors a1

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Frank Kalis SSC Guru Points: 111183 More actions · Answer 6

...and I forgot, I haven't verfiy this myself, but I was told that the difference in execution plans is getting bigger with SQL Server 2005.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 7

Probably another VERY GOOD reason that I forget about COALESCE

Thank you as always for keeping me from hurting myself

Good Hunting!

AJ Ahrens

webmaster@kritter.net

David Burrows SSC Guru Points: 65143 More actions · Answer 8

AJ,

If your interested this would be the solution using COALESCE

COALESCE(NULLIF(Col1,'HHHH'), Col2) AS [Result]

Far away is close at hand in the images of elsewhere.
Anon.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 9

Hey, how do you say: Mr. Knows-it-all ? scnr

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

PBirch SSCrazy Eights Points: 8493 More actions · Answer 10

I just coalesce when I'm in meetings with clients. It makes me sound important and dba-ish.

Quand on parle du loup, on en voit la queue

Frank Kalis SSC Guru Points: 111183 More actions · Answer 11

Okay, that's acceptable.

I guess you would be lost without an instrument that could deal with missing information in that clientel anyway.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]