CODEWITHSUNDEEP
sqlsql-servert-sqlgrouping
alesic
alesic

Reputation: 197

TSQL, How can I group rows?

In SQL Server 2008, I have this result:

 contact  phone       address    email
 Name1    435551123     
 Name1                street 1  
 Name1                           [email protected]
 Name2    654987624
 Name2                street2   
 Name2                           [email protected]

and I want to "compress" it like the following:

 contact  phone       address    email
 Name1    435551123   street 1   [email protected]       
 Name2    654987624   street2    [email protected]

Upvotes: 4

Views: 140

Answers (2)

Tim Schmelter
Tim Schmelter

Reputation: 460058

You could use a CTE and some OVER clauses:

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        [email protected]
Name2   654987624   street2         [email protected]

DEMO

Upvotes: 2

Prahalad Gaggar
Prahalad Gaggar

Reputation: 11599

Try the Query

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

SQL Fiddle

Upvotes: 8

Related Questions