TSQL, How can I group rows?

Question

In SQL Server 2008, I have this result:

contact phone address email Name1 435551123 Name1 street 1 Name1 [e-mail] Name2 654987624 Name2 street2 Name2 [e-mail] 

and I want to “squeeze” it, as shown below:

  contact phone address email Name1 435551123 street 1 [e-mail] Name2 654987624 street2 [e-mail] 

Answer 1

Try to complete the request

 select contact, max(phone), max(address), max(email) from table_name group by contact 

SQL Fiddle

Answer 2

You can use CTE and some OVER articles :

 WITH CTE AS ( SELECT [contact], [phone]=MAX(PHONE)OVER(PARTITION BY CONTACT), [address]=MAX(address)OVER(PARTITION BY CONTACT), [email]=MAX(email)OVER(PARTITION BY CONTACT), RN = Row_number() OVER( partition BY [contact] ORDER BY [contact]) FROM dbo.Address) SELECT [contact], [phone], [address], [email] FROM CTE WHERE RN = 1 

Result:

 CONTACT PHONE ADDRESS EMAIL Name1 435551123 street 1 [e-mail] Name2 654987624 street2 [e-mail] 

Demo