SQL Help - Merge rows to single data row

Question

Hello,

I have a table with data rows like below, i would like to merge these rows such that i have one row per client with all the data in it.  I am using SQL2005. I looked at other posts on this topic but the requirements are different than below.

For example:

Client ID         ClientName         Client DOB          Client Gender         DateEnroll          DateClosed        

1                    John, Doe            1/1/1965               Male                     1/1/2010           NULL

1                    John, Doe            1/1/1965               Male                     NULL                   5/1/2012

2                   Test, Me                4/3/1981              Female                 NULL                   NULL

2                   Test, Me                4/3/1981              Female                 NULL                   10/10/2011

2                   Test, Me                4/3/1981              Female                7/10/2012           NULL

3                   Matt, King             8/10/1971             Male                    4/1/2008             9/10/2010

4                   John, Smith            5/9/1955             Male                     NULL                    NULL

Output desired

Client ID         ClientName         Client DOB          Client Gender         DateEnroll          DateClosed        

1                    John, Doe            1/1/1965               Male                     1/1/2010           5/1/2012

2                   Test, Me                4/3/1981              Female                 7/10/2012           10/10/2011

3                   Matt, King             8/10/1971             Male                    4/1/2008             9/10/2010

4                   John, Smith            5/9/1955             Male                     NULL                    NULL

Any guidance would be very much appreciated.

Regards

Answer 1

hi,

try this:

select clientid, clientname, clientDOB, ClientGender, max(dateenroll), max(dateclosed) from
yourtable group by clientid, clientname, clientDOB, ClientGender

---

Regards, Nico

Answer 2

Hi,

select ClientID
, clientName
, ClintDOB
, ClientGender
, max(DateEnroll) as DateEnroll
, max(DateClosed) as DateClosed
from Table
group by ClientID
, clientName
, ClintDOB
, ClientGender

---

- Chintak (My Blog)

Answer 3

select ClientID, max(ClientName) as ClientName, Max(CliendDOB) as ClientDOB, max(ClientGender) as ClientGender, max(DateEnroll) as DateEnroll, max(DateClosed) as DateClosed from ClientInfo GROUP BY ClientID

--------------------------------------------

This should work for the sample you posted.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 4

If there are other columns not date but varchar that i would like to include then how do i approach it?

example:

Client ID         ClientName         Client DOB          Client Gender         DateEnroll          DateClosed       MedTaken 

1                    John, Doe            1/1/1965               Male                     1/1/2010           NULL NULL

1                    John, Doe            1/1/1965               Male                     NULL                   5/1/2012 Yes

2                   Test, Me                4/3/1981              Female                 NULL                   NULL No

2                   Test, Me                4/3/1981              Female                 NULL                   10/10/2011 NULL

2                   Test, Me                4/3/1981              Female                7/10/2012           NULL No

3                   Matt, King             8/10/1971             Male                    4/1/2008             9/10/2010 NULL

4                   John, Smith            5/9/1955             Male                     NULL                    NULL Yes

Then output needs to be

Client ID         ClientName         Client DOB          Client Gender         DateEnroll          DateClosed         MedTaken

1                    John, Doe            1/1/1965               Male                     1/1/2010           5/1/2012 Yes

2                   Test, Me                4/3/1981              Female                 7/10/2012           10/10/2011 No

3                   Matt, King             8/10/1971             Male                    4/1/2008             9/10/2010 NULL

4                   John, Smith            5/9/1955             Male                     NULL                    NULL Yes

Thanks

Answer 5

you'd do the same... max(somefield)

---

Regards, Nico

Answer 6

Hi 

Try this

select clientid, clientname, min(clientDOB), ClientGender, MIN(dateenroll), MAX(dateclosed) from
yourtable group by clientid, clientname, clientDOB, ClientGender

Regards
satheesh

Answer 7

Thanks Nico, Naomi, Chintak and satheesh. I will do that. I appreciate your prompt feedback. As Nico first replied, i will mark his as answer but thanks to others.

Answer 8

You can mark all correct answers as answer. Say, I answered correctly also based on your data. I am having very bad connection where I am right now which may account for some delay in my responses.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog