combine sql rows

johnnyg123 used Ask the Experts™
here is some sample data from  a sql 2012 table named EmailAddress

ReportName        Region       First    Last         Email                                      MailTo

Region                       1              John      Doe              To      
Region                         1              John      Smith                      cc      
Region                     1               Jane      Doe                       bcc
Region                       2              Adam      Doe              To      
Region                         2              Adam      Smith                      cc      

Trying to write query that will return the different email types in one row as follows

ReportName   Region ToEmail                               CCEmail                  BccEmail
Region               1          
Region               2
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018
What you want is called a PIVOT:
select * from (
select reportname, region, email, mailto from emailaddress
) x
pivot (
 for MailTo in ([To],[cc],[bcc])
) p;

Open in new window

Working example here:
Perfect!  Thanks!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial