469,286 Members | 2,522 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,286 developers. It's quick & easy.

How to combine multiple input fields as single output field

Hi,
I need to data from several input fields, and treat them as if
they were all the same field. I'm not describing this well, so I'll
give an example:

I have a table like this:

Name Favorite 1: Favorite 2: Favorite 3:
Bob Apple Banana Cherry
Ann Pear Peach Apple

And I need output like this:

Name Fruit
Bob Apple
Bob Banana
Bob Cherry
Ann Pear
Ann Peach
Ann Apple

Is there an easy way to do this? I can create table, and then use
several insert queries to pull the data and populate the table (3
above, but 30 in the tables I've got), but I'm thinking there must be
an easier way.

Nov 21 '06 #1
14 2100
Pythor wrote:
Hi,
I need to data from several input fields, and treat them as if
they were all the same field. I'm not describing this well, so I'll
give an example:

I have a table like this:

Name Favorite 1: Favorite 2: Favorite 3:
Bob Apple Banana Cherry
Ann Pear Peach Apple

And I need output like this:

Name Fruit
Bob Apple
Bob Banana
Bob Cherry
Ann Pear
Ann Peach
Ann Apple

Is there an easy way to do this? I can create table, and then use
several insert queries to pull the data and populate the table (3
above, but 30 in the tables I've got), but I'm thinking there must be
an easier way.
select name, favorite_1 as fruit
from mytable
union all
select name, favorite_2 as fruit
from mytable
union all
select name, favorite_3 as fruit
from mytable
order by 1;

Art S. Kagel
Nov 21 '06 #2

Art S. Kagel wrote:
select name, favorite_1 as fruit
from mytable
union all
select name, favorite_2 as fruit
from mytable
union all
select name, favorite_3 as fruit
from mytable
order by 1;

Art S. Kagel
OK. Thanks. Looks like I'm in for a long period of Copy-Paste.
Better than the create table alternative I was thinking of, though.

Nov 21 '06 #3
Yes--you're better off not hard-coding the number of favorites, but
moving the table to 1NF. However, should this come up again, take a
look at Serge's posting titled "How do I SELECT multiple columns as one
column," which discusses the use of the VALUES construct for just such
a situation.

--Jeff

Pythor wrote:
Art S. Kagel wrote:
select name, favorite_1 as fruit
from mytable
union all
select name, favorite_2 as fruit
from mytable
union all
select name, favorite_3 as fruit
from mytable
order by 1;

Art S. Kagel

OK. Thanks. Looks like I'm in for a long period of Copy-Paste.
Better than the create table alternative I was thinking of, though.
Nov 21 '06 #4
If the table is large, this may be preferable.
Because this will scan the table only once compared with 3 times at the
time of using union all.
SELECT Name
, MAX(CASE n
WHEN 1 THEN Favorite_1
WHEN 2 THEN Favorite_2
WHEN 3 THEN Favorite_3
END) AS Fruit
FROM Favorites
, (VALUES 1,2,3) P(n)
GROUP BY
Name, n

Nov 22 '06 #5

Tonkuma wrote:
If the table is large, this may be preferable.
Because this will scan the table only once compared with 3 times at the
time of using union all.
SELECT Name
, MAX(CASE n
WHEN 1 THEN Favorite_1
WHEN 2 THEN Favorite_2
WHEN 3 THEN Favorite_3
END) AS Fruit
FROM Favorites
, (VALUES 1,2,3) P(n)
GROUP BY
Name, n
Wow, Tonkuma, nice solution (as usual).

A question though, why are you using a GROUP BY?

B.

Nov 22 '06 #6

Brian Tkatch wrote:
Tonkuma wrote:
If the table is large, this may be preferable.
Because this will scan the table only once compared with 3 times at the
time of using union all.
SELECT Name
, MAX(CASE n
WHEN 1 THEN Favorite_1
WHEN 2 THEN Favorite_2
WHEN 3 THEN Favorite_3
END) AS Fruit
FROM Favorites
, (VALUES 1,2,3) P(n)
GROUP BY
Name, n

Wow, Tonkuma, nice solution (as usual).

A question though, why are you using a GROUP BY?

B.
Yes... Excellent. Unfortunately, my SQL interpreter doesn't accept
"From ..., Values (...) ..." in a select statement. It tries to
interpret Values as a table name. Mind you, "insert into Blah values
(x, y, z)" works fine.

Lee

Nov 22 '06 #7
Pythor wrote:
>
Brian Tkatch wrote:
>Tonkuma wrote:
If the table is large, this may be preferable.
Because this will scan the table only once compared with 3 times at the
time of using union all.
SELECT Name
, MAX(CASE n
WHEN 1 THEN Favorite_1
WHEN 2 THEN Favorite_2
WHEN 3 THEN Favorite_3
END) AS Fruit
FROM Favorites
, (VALUES 1,2,3) P(n)
GROUP BY
Name, n

Wow, Tonkuma, nice solution (as usual).

A question though, why are you using a GROUP BY?

B.

Yes... Excellent. Unfortunately, my SQL interpreter doesn't accept
"From ..., Values (...) ..." in a select statement. It tries to
interpret Values as a table name. Mind you, "insert into Blah values
(x, y, z)" works fine.
Try:

SELECT ...
FROM TABLE ( VALUES ( ...) AS p(n)

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Nov 22 '06 #8

Knut Stolze wrote:
[...]
SELECT ...
FROM TABLE ( VALUES ( ...) AS p(n)
<nitpicking>
select ... from LATERAL( VALUES ( ...) ) AS p(n)
</nitpicking>

/Lennart

Nov 22 '06 #9

Lennart wrote:
Knut Stolze wrote:
[...]
SELECT ...
FROM TABLE ( VALUES ( ...) AS p(n)

<nitpicking>
select ... from LATERAL( VALUES ( ...) ) AS p(n)
</nitpicking>

/Lennart
I tried that one, and it didn't work either. I'll have to try the
Table version when I get back to work.

Nov 22 '06 #10
Lennart wrote:
>
Knut Stolze wrote:
[...]
>SELECT ...
FROM TABLE ( VALUES ( ...) AS p(n)

<nitpicking>
select ... from LATERAL( VALUES ( ...) ) AS p(n)
</nitpicking>
You are right, but I'm not wrong either :-) - if you have a look at note (2)
for *table-reference* here:

http://publib.boulder.ibm.com/infoce...c/r0000875.htm

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Nov 23 '06 #11
Brian Tkatch wrote:
Tonkuma wrote:
If the table is large, this may be preferable.
Because this will scan the table only once compared with 3 times at the
time of using union all.
SELECT Name
, MAX(CASE n
WHEN 1 THEN Favorite_1
WHEN 2 THEN Favorite_2
WHEN 3 THEN Favorite_3
END) AS Fruit
FROM Favorites
, (VALUES 1,2,3) P(n)
GROUP BY
Name, n

Wow, Tonkuma, nice solution (as usual).

A question though, why are you using a GROUP BY?

B.
Brian, you are right. Thank you to point out my carelesness. GROUP BY
is not necessary.
Perhaps I confused with other case.

Nov 23 '06 #12
Pythor wrote:
Brian Tkatch wrote:
Tonkuma wrote:
If the table is large, this may be preferable.
Because this will scan the table only once compared with 3 times at the
time of using union all.
SELECT Name
, MAX(CASE n
WHEN 1 THEN Favorite_1
WHEN 2 THEN Favorite_2
WHEN 3 THEN Favorite_3
END) AS Fruit
FROM Favorites
, (VALUES 1,2,3) P(n)
GROUP BY
Name, n
Wow, Tonkuma, nice solution (as usual).

A question though, why are you using a GROUP BY?

B.

Yes... Excellent. Unfortunately, my SQL interpreter doesn't accept
"From ..., Values (...) ..." in a select statement. It tries to
interpret Values as a table name. Mind you, "insert into Blah values
(x, y, z)" works fine.

Lee
What is OS and Version of your DB2?
Anyway, doesn't following work in your environment?
SELECT Name
, CASE n
WHEN 1 THEN Favorite_1
WHEN 2 THEN Favorite_2
WHEN 3 THEN Favorite_3
END AS Fruit
FROM Favorites
, (SELECT 1 FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT 2 FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT 3 FROM SYSIBM.SYSDUMMY1) P(n)
ORDER BY Name, n

Nov 23 '06 #13
Knut Stolze wrote:
Lennart wrote:
>Knut Stolze wrote:
[...]
>>SELECT ...
FROM TABLE ( VALUES ( ...) AS p(n)
<nitpicking>
select ... from LATERAL( VALUES ( ...) ) AS p(n)
</nitpicking>

You are right, but I'm not wrong either :-) - if you have a look at note (2)
for *table-reference* here:

http://publib.boulder.ibm.com/infoce...c/r0000875.htm
*pst*Your brackets were not balanced...

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

WAIUG Conference
http://www.iiug.org/waiug/present/Fo...Forum2006.html
Nov 23 '06 #14

Knut Stolze wrote:
[...]
You are right, but I'm not wrong either :-) - if you have a look at note (2)
for *table-reference* here:
Yes, I know. I have been using the table construct for so long that I'm
having some difficulties converting to LATERAL, and thats why it rings
a bell each time I see one :-)

/Lennart

Nov 23 '06 #15

This discussion thread is closed

Replies have been disabled for this discussion.

By using this site, you agree to our Privacy Policy and Terms of Use.