consolidate results - 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 2000 Forums

SQL Server Development (2000)

consolidate results

Author

Topic

lane0618
Posting Yak Master

134 Posts

Posted - 2003-05-01 : 20:20:02

I have the follwing query:

SELECT nodePairA, nodePairB
FROM Tree2TreePair
WHERE (nodePairA = 'A08') OR
(nodePairB = 'A08')

results:

nodePairA nodePairB
A08 A0B
A08 A10
A08 A16
A08 A18
A08 A22
A21 A08
A20 A08

I want to end up with a single column with any set that has A08 in it, so I wound end up with:

ConsolidatedPair
A0B
A10
A16
A18
A22
A21
A20

Thanks in Advance!

spromtet
Starting Member

7 Posts

Posted - 2003-05-01 : 21:09:57

I think this should work. I might not understand what you're trying to do.

SELECT
ConsolidatedPair =
CASE
WHEN nodePairA= 'A08' THEN nodePairB
WHEN nodePairB = 'A08' THEN nodePairA
END
FROM
Tree2TreePair
WHERE
(nodePairA = 'A08') OR (nodePairB = 'A08')

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-05-01 : 21:10:11

SELECT
case when nodepairA = 'A08' then NodePairB else NodePaidA end as Node
FROM Tree2TreePair
WHERE (nodePairA = 'A08') OR
(nodePairB = 'A08')

- Jeff

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-05-01 : 21:11:17

Ah! you beat me by like 10 seconds. but mine is shorter! you don't need to test for two conditions -- the WHERE clause takes care of that.

- Jeff

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-05-01 : 21:54:08

SELECT  
case when nodepairA = 'A08' then NodePairB else NodePaidA end as Node 
FROM Tree2TreePair 
WHERE 'A08' IN (nodePairA, nodePairB)

And that is shorter still.....

DavidM

"SQL-3 is an abomination.."

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-05-02 : 04:28:21

Can I play?


SELECT COALESCE(NULLIF(nodePairA, 'A08'), nodePairB) AS Node
FROM Tree2TreePair 
WHERE 'A08' IN (nodePairA, nodePairB)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources