combine sql rows

johnnyg123
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       JohnDoe@email.com              To      
Region                         1              John      Smith      jrs@email.com                      cc      
Region                     1               Jane      Doe        jd@email.com                       bcc
Region                       2              Adam      Doe       AdamDoe@email.com              To      
Region                         2              Adam      Smith      ars@email.com                      cc      



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


ReportName   Region ToEmail                               CCEmail                  BccEmail
Region               1          JohnDoe@email.com        jrs@email.com      jd@email.com          
Region               2          AdamDoe@email.com     ars@email.com
Comment
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 (
 max(email)
 for MailTo in ([To],[cc],[bcc])
) p;

Open in new window


Working example here:
https://dbfiddle.uk/?rdbms=sqlserver_2012&fiddle=f6045df9f36c8326eb1560eace42d3f8
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