How to combine different row into one row

Dear all,
I got a table with different columns of same client id as
follows:
e.g.
ClientID Name Age Country
1 Peter
1 32
1 China
Now I want to combine these three rows into one rows as
follows
ClientID Name Age Country
1 Peter 32 China
How can I do that by SQL statements. Just simple SQL
statements.
Not sotred procedures.
Thankshon123456 wrote:
> Dear all,
> I got a table with different columns of same client id as
> follows:
> e.g.
> ClientID Name Age Country
> 1 Peter
> 1 32
> 1 China
>
> Now I want to combine these three rows into one rows as
> follows
> ClientID Name Age Country
> 1 Peter 32 China
> How can I do that by SQL statements. Just simple SQL
> statements.
> Not sotred procedures.
>
> Thanks
What do the spaces in your example represent? What is the key of this
table? If those missing values are always just empty then do:
SELECT clientid,
MAX(name) AS name,
MAX(age) AS age,
MAX(country) AS country
FROM your_table
GROUP BY clientid ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Hi
CREATE TABLE #test (col1 INT, col2 CHAR(1),col3 INT,col4 CHAR(1))
INSERT INTO #Test VALUES (1,'a',NULL,NULL)
INSERT INTO #Test VALUES (1,NULL,32,NULL)
INSERT INTO #Test VALUES (1,NULL,NULL,'b')
SELECT MAX(col1),MAX(col2),MAX(col3),MAX(col4)
FROM #test
--or
SELECT col1,
MAX(CASE WHEN col2 IS NOT NULL THEN col2 end)as col2,
MAX(CASE WHEN col3 IS NOT NULL THEN col3 end)as col3,
MAX(CASE WHEN col4 IS NOT NULL THEN col4 end)as col4
FROM #test
GROUP BY col1
"hon123456" <peterhon321@.yahoo.com.hk> wrote in message
news:1140524835.788163.177040@.g43g2000cwa.googlegroups.com...
> Dear all,
> I got a table with different columns of same client id as
> follows:
> e.g.
> ClientID Name Age Country
> 1 Peter
> 1 32
> 1 China
>
> Now I want to combine these three rows into one rows as
> follows
> ClientID Name Age Country
> 1 Peter 32 China
> How can I do that by SQL statements. Just simple SQL
> statements.
> Not sotred procedures.
>
> Thanks
>|||SELECT ClientID, MIN(Name) as Name, Min(Age) as Age, Min(Country) as
Country
FROM Table
GROUP BY ClientID
Stu|||Thanks for you all.