By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,687 Members | 2,228 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,687 IT Pros & Developers. It's quick & easy.

How to combine fields by SQL

P: n/a
Dear all,

I got a table with three rows like the following

clientid name age location

1 peter
1 32
1 China
I want to combine the three rows into one row like the follwoing

clientid name age location
1 peter 32 China.

Please suggest how can I do that in SQL.

Thanks.

Feb 21 '06 #1
Share this Question
Share on Google+
5 Replies


P: n/a
hon123456 wrote:
Dear all,

I got a table with three rows like the following

clientid name age location

1 peter
1 32
1 China
I want to combine the three rows into one row like the follwoing

clientid name age location
1 peter 32 China.

Please suggest how can I do that in SQL.

SELECT MAX(name) AS name, MAX(age) AS age, MAX(location) as location
FROM T GROUP BY clientid

Thsi works for all columns for which comparison is defined.
I assume that the "empty" values are NULL. If not you may want to add a:
MAX(CASE WHEN location <> '' THEN location END) AS location and so on.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Feb 21 '06 #2

P: n/a
I would have tried:

SELECT
(SELECT name FROM T WHERE clientid = outer.clientid and name is not
null) name
(SELECT age FROM T WHERE clientid = outer.clientid and age is not
null) age
(SELECT location FROM T WHERE clientid = outer.clientid and location
is not null) location
FROM T outer

B.

Feb 21 '06 #3

P: n/a
Brian Tkatch wrote:
I would have tried:

SELECT
(SELECT name FROM T WHERE clientid = outer.clientid and name is not
null) name
(SELECT age FROM T WHERE clientid = outer.clientid and age is not
null) age
(SELECT location FROM T WHERE clientid = outer.clientid and location
is not null) location
FROM T outer


And a third solution:

VALUES ( ( SELECT name FROM t WHERE clientid = 1 ),
( SELECT age FROM t WHERE clientid = 1 ),
( SELECT location FROM t WHERE clientid = 1 ) )

And a fourth:

SELECT t1.name, t2.age, t3.location
FROM t AS t1 JOIN t AS t2 ON t1.clientid = t2.clientid JOIN
t AS t3 ON t1.clientid = t3.clientid

You could also move tho join-condition to the WHERE clause but this is
usually considered as bad practice because the WHERE clause is used to
restrict the set of rows produced by the FROM clause and not to implement a
join condition.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Feb 21 '06 #4

P: n/a
>You could also move tho join-condition to the WHERE clause but this is
usually considered as bad practice because the WHERE clause is used to
restrict the set of rows produced by the FROM clause and not to implement a
join condition.


WHERE clauses always restrict, as the FROM statement causes a
cartesian-join without it. I prefer putting it in the WHERE clause, it
just makes sense to me that way. And, i find the ANSI syntax terribly
confusing in practice, but that is more likely to be from lack of use.

B.

Feb 21 '06 #5

P: n/a
Thanks for you all

Feb 22 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.