locked
Pivot 2 Columns - String only RRS feed

  • Question

  • Hello,

    I have an issue with getting string values from a two column table to display the right way.

    My table is as follows:

    [Company Name], [email address]

    The challenge is that a company name can appear twice be cause it have 2 different values for email:

    company A - email1@companya.com

    company A - email2@companya.com

    company B - email1@companya.com

    I am trying to pivot the table to so that it looks like :

    [Company Name] [email address 1] [email address 2]

    company A email1@companya.com email2@companya.com

    company B email1@companya.com

    Alternatively, I could also use the original layout, provided email values for the the same company are concatenated into one field:

    [Company Name] [email address 1] 

    company A email1@companya.com ,email2@companya.com 

    Any help would be appreciated.

    Thanks

    Thursday, May 16, 2013 8:45 PM

Answers

  • with cte as (
    select [Company Name], [email address], 
    row_number() over (partition by [Company Name]
    order by [email address]) as RowNum
    from tbl1
    )
    select * 
    from cte
    pivot (max([email address])
    for RowNum in ([1],[2])) as pvt


    Russel Loski, MCT, MCSA SQL Server 2012, 2008, MCITP Business Intelligence Developer and Database Developer 2008 Twitter: @sqlmovers; blog: www.sqlmovers.com

    Thursday, May 16, 2013 9:12 PM