CODEWITHSUNDEEP
sqldb2
jjLin
jjLin

Reputation: 3291

How to combine two records into one line?

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.

Upvotes: 3

Views: 16998

Answers (3)

dan1111
dan1111

Reputation: 6566

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.

Upvotes: 1

almi_n
almi_n

Reputation: 51

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

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269923

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

Upvotes: 0

Related Questions