moving multiple and variable rows to columns

Question

Trying to move multiple rows for each users to columns. There are different number of rows for some users, for instance, 2 or three emails, or 1 plus phone number. this differs depending on the user account.

Looks something like this.

Id  FirstName  LastName     Listing              phonetype   listingTypeID

1   Judy       Johnson      judyJ@email.com      null        4
1   Judy       Johnson      555-555-2020         home        2
1   Judy       Johnson      555-555-2022         home        2
1   Judy       Johnson      NULL                 null        1
2   Jim        Brown        555-555-3333         cell        2
2   Jim        Brown        jbrown@email.com     null        4
3   John       White        NULL                 null        1
3   John       White        johnwhite@email.com  null        4

The null values represent the address, which I figured out how to do. I have seen this example quite a few times on stackflow, but not the variable number of rows per user. I realize that some columns will be empty for some users, but looking to get something like this.

Id  FirstName   LastName    email              email2             email3    phone           phone2        phone3

1   Judy        Johnson     judyJ@email.com                                 555-555-2020    555-555-2022    
2   Jim         Brown       jbrown@email.com   jbrown33@info.com            555-555-3333    555-555-2332    
3   John        White       jwhite@email.com

This is what I have and it works, but when there are multiple emails or phones with the same type (i.e., home or cell) there is no new column created. I'm thinking some type of If statement but can't figure it out.

SELECT  id ,
        firstname ,
        lastname ,
        middlename ,
        prefix ,
        suffix ,
        notes ,
        MAX(line1) address ,
        MAX(line2) address2 ,
        MAX(city) city ,
        MAX(state) state ,
        MAX(zip) zip ,
        MAX(county) county ,
        MAX(country) country ,
        MAX(CASE WHEN phonetype = 'Cell' THEN listing END) Cell ,
        MAX(CASE WHEN phonetype = 'Home' THEN listing END) Home ,
        MAX(CASE WHEN phonetype = 'Office' THEN listing END) Office ,
        MAX(CASE WHEN phonetype = 'Fax' THEN listing END) Fax ,
        MAX(CASE WHEN listingTypeID = 4 THEN listing END) email ,
        MAX(CASE WHEN listingTypeID = 5 THEN listing END) weblink
FROM    [Matrix_Copy].[dbo].[Indiv]
GROUP BY id ,
        firstname ,
        lastname ,
        middlename ,
        prefix ,
        suffix ,
        notes
ORDER BY id

thanks for any help.

possible duplicate of [Dynamic SQL Server Pivot ( UNPIVOT ) column name to a row value](http://stackoverflow.com/questions/8917108/dynamic-sql-server-pivot-unpivot-column-name-to-a-row-value) — Stuart Ainsworth, May 05 '14 at 17:29
not sure this is the same thing, as I just need to determine how to create the known column when there is a duplicate. Thanks. — user3002720, May 05 '14 at 17:39
@user3002720 take a look at [this](http://stackoverflow.com/questions/20999264/transpose-rows-and-columns-with-no-aggregate) or [this](http://stackoverflow.com/questions/21320447/pivot-dynamically-returned-results-from-join-of-two-tables) or [this](http://stackoverflow.com/questions/18851588/pivot-rows-to-columns-with-more-than-1-value-returned) — Taryn, May 05 '14 at 17:56
There is no `phonetype` nor `listingtypeid` column in your sample data — dean, May 05 '14 at 18:01

Dan · Accepted Answer · 2014-05-06T08:39:04.730

You can use PIVOT to do exactly what you want. The trick is to construct a key that is unique for each listing. This can be done using ROW_NUMBER, to ensure that if a user has more than one phone number, they will be identified as 1, 2, 3, etc... concatenate this value with the ListingTypeId to get [2-1], [2-2], [2-3], etc. and then you're basically done:

SELECT [Id], [Firstname], [Lastname], 
       [4-1] AS [Email], [4-2] AS [Email2],
       [2-1] AS [Phone], [2-2] AS [Phone2], [2-3] AS [Phone3]

FROM (
      SELECT *, 
        CAST(ListingTypeId AS NVARCHAR) + '-' + 
        CAST(ROW_NUMBER() OVER (PARTITION BY Id, ListingTypeId ORDER BY ListingTypeId) AS NVARCHAR) AS ListingTypeKey
      FROM
          [Matrix_Copy].[dbo].[Indiv]
      ) AS SourceTable
PIVOT (
    MAX(Listing)
    FOR ListingTypeKey IN ([4-1], [4-2], [2-1], [2-2], [2-3])
) AS PivotTable

Now, if you want to return more than two email addresses or more than three phone numbers, simply add them to the list in the SELECT part of the statement, as well as in the FOR ListingTypeKey IN. Email addresses will be numbered [4-1], [4-2], [4-3], etc. while Phone numbers will be numbered [2-1], [2-2], [2-3], etc.

I had seen the pivot function many times, but couldn't figure out how to use it for my data. This is very helpful. I appreciate your help. — user3002720, May 06 '14 at 12:21
You can show your appreciation by marking my post as an answer to your question :-) — Dan, May 06 '14 at 13:20

moving multiple and variable rows to columns

1 Answers1