Combining Columns into one output field?

Question

Post reply

Combining Columns into one output field?

jackimo

SSCommitted

Points: 1885
More actions
March 13, 2008 at 6:29 am

#183905

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!

Viewing 6 posts - 1 through 5 (of 5 total)

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

Adam Haines SSC-Insane Points: 23217 More actions · Answer 1

One way is to insert the needed data into a temporary table.

DECLARE @t TABLE(

customer1 varchar(25),

customer2 varchar(25),

customer3 varchar(25)

)

insert into @t

select 'SMITH','JONES','WILSON' union all

select 'EDWARDS','SMICKS','SMOKES' union all

select 'SMITH','TODD','HELP'

DECLARE @Results TABLE(

Customer varchar(25)

)

INSERT INTO @Results

SELECT customer1

from @t

where customer1 like 'sm%' UNION ALL

SELECT customer2

from @t

where customer2 like 'sm%' UNION ALL

SELECT customer3

from @t

where customer3 like 'sm%'

SELECT DISTINCT Customer

FROM @Results

ORDER BY Customer

My blog: http://jahaines.blogspot.com

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 2

... or, you can just use the power of UNION...

SELECT customer1

from yourtable

where customer1 like 'sm%' UNION

SELECT customer2

from yourtable

where customer2 like 'sm%' UNION

SELECT customer3

from yourtable

where customer3 like 'sm%'

Real key is the person that built such a denormalized table needs to be fed some porkchops...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jackimo SSCommitted Points: 1885 More actions · Answer 3

Thanks all! These posts provided answers to all my questions!

Adam Haines SSC-Insane Points: 23217 More actions · Answer 4

... or, you can just use the power of UNION...

What's funny is our code is exactly the same, minus the temp table. I was only thinking about inserting into the temp table using union and not using the union to select the data.

I think it's time for CAFFEINE :hehe:

My blog: http://jahaines.blogspot.com

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 5

Heh... it's 'cause I copied directly from your post 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)