How to combine two records into one line?

Question

Suppose I have a table with columns id, name, phone_type, phone_no

and I have 2 records

{1, Tom, home, 123}
{2, Tom, mobile, 234}

If I just use sql:

SELECT * FROM table WHERE name = tom;

It will show both records.

However, I would like to display in one line like:

Tom, mobile,234,home,123

something like that...

How can I modify the sql in db2?

Please help.

**What** database system, and which version?? *SQL* is just the *Structured Query Language* - a language used by many database systems, but not a a database product... features like this is often vendor-specific - so we really need to know what **database system** you're using.... — marc_s, Aug 13 '12 at 14:54
Look at the answers in http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string — Artem Pianykh, Aug 13 '12 at 14:55
Are there ***always*** only two types of phone? And are these ***always*** `'mobile'` and `'home'`? If not, please could you describe every possible case and what output you want? *(NOTE: If you can have any number of phone types, it is actually standard practice to leave the data exactly as you already have it.)* — MatBailie, Aug 13 '12 at 14:57
some people have only mobile and some people may have mobile and home — jjLin, Aug 13 '12 at 15:17

score 1 · Answer 1 · answered Aug 14 '12 at 10:31

Here is a more generic example using OLAP functions. This will get the first four pairs of phone type and phone number for each name. If someone has less than four, the remaining ones will be filled with NULL. It is obvious how you could expand this to more than four.

select * from (
    select id,
           min(id) over (partition by name) as first_id,
           name,
           phone_type as phone_type1,
           phone_no as phone_no1,
           lead(phone_type,1) over (partition by name order by id) as phone_type2,
           lead(phone_no,1) over (partition by name order by id) as phone_type2,
           lead(phone_type,2) over (partition by name order by id) as phone_type3,
           lead(phone_no,2) over (partition by name order by id) as phone_type3,
           lead(phone_type,3) over (partition by name order by id) as phone_type4,
           lead(phone_no,3) over (partition by name order by id) as phone_type4
   from table
) where id = first_id

The outer select guarantees that you only get one row per person. You need this because the result of an OLAP function (in this case min(id)) can't be put directly into a where clause.

Not all versions of DB2 support `lead()`, although the OP didn't list which version he's using. This also limits the results to 4 phone numbers - If all the OP needs is 2, I might just try a self-join, rather than attempting to use the OLAP function; if he needs more than 4, you're going to need something else... — Clockwork-Muse, Aug 14 '12 at 16:22
As I mentioned, this would be easy to expand to get more than 4. Just add more pairs of lead() lines until you can handle the maximum case possible with your data. No, this solution isn't pretty, but there isn't an elegant way to do this in DB2, unfortunately. — dan1111, Aug 16 '12 at 08:13

score 0 · Accepted Answer · answered Aug 13 '12 at 14:54

0

Here is one way:

select name,
       'mobile',
       max(case when ttype = 'mobile' then phone end) as mobilephone,
       'home',
       max(case when ttype = 'home' then phone end) as homephone
from t
group by name

answered Aug 13 '12 at 14:54

Gordon Linoff

1,242,037
58
646
786

score 0 · Answer 3 · answered Aug 13 '12 at 15:05

0

this might give an idea

declare @rVal nvarchar(128)
set @rVal = 'Tom'
select @rVal = @rval + coalesce(',' + phoneType + ',' + convert(nvarchar,phoneNumber),'') from testTable where name = 'Tom'
select @rVal

answered Aug 13 '12 at 15:05

almi_n

51
3

This isn't valid syntax on my version of DB2 (on the iSeries), and doesn't return the rows concatenated anyways (only the columns, which is kind of silly). And that `COALESCE()` is useless, as it'll always have data (because of the `','`). And if you're expecting this to be run in a loop... you need to stop thinking like an imperative programmer when dealing with SQL (you're fighting the system). – Clockwork-Muse Aug 14 '12 at 16:17

How to combine two records into one line?

3 Answers3