sql or stored proc help ... - 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)

sql or stored proc help ...

Author

Topic

jackimo
Starting Member

1 Post

Posted - 2008-03-13 : 08:43:19

I am trying to 'combine' the output of fields into one column.
I have rows in a table like this

CUSTOMER1 CUSTOMER2 CUSTOMER3
SMITH JONES WILSON
EDWARDS SMICKS SMOKES
SMITH TODD HELP

I would like to see a distinct list of customer fields where the name is LIKE (SM%)
So the output would be

SMITH (notice this is distinct)
SMICKS
SMOKES

Any ideas or comments would be greatly appreciated - Thanks!

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-03-13 : 08:46:49

[code]select distinct customer from
(
Select customer1 as customer from table where customer1 LIKE 'SM%'
union all
Select customer2 as customer from table where customer2 LIKE 'SM%'
union all
Select customer3 as customer from table where customer3 LIKE 'SM%'
) temp[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-13 : 08:54:13

Depending on the number of records, this can be an alternaive

SELECT Customer1 AS Customer FROM Table1 WHERE Customer1 LIKE 'SM%'
UNION
SELECT Customer2 FROM Table1 WHERE Customer2 LIKE 'SM%'
UNION
SELECT Customer3 FROM Table1 WHERE Customer3 LIKE 'SM%'

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources