Search code examples
sql-servert-sqlpivotunpivot

Pivot/Unpivot and Ranking paired data

in a table where each row has 9 matched pairs is it possible to use Pivot to produce columns, so for example the following:

SELECT TOP 1
    ID,
    HOME_APPDTE1,
    HOMENUMBER1,
    HOME_APPDTE2,   
    HOMENUMBER2, 
    HOME_APPDTE3,  
    HOMENUMBER3,  
    WORK_APPDTE1,   
    WORKNUMBER2,
    WORK_APPDTE2,
    WORKNUMBER3,
    WORK_APPDTE3,
    WORKNUMBER1,
    MOBILE_APPDTE1,
    MOBILENUMBER1,
    MOBILE_APPDTE2,
    MOBILENUMBER2,
    MOBILE_APPDTE3, 
    MOBILENUMBER3
FROM 
    Telephone_Numbers

returns

ID  HOME_APPDTE1    HOMENUMBER1 HOME_APPDTE2    HOMENUMBER2 HOME_APPDTE3    HOMENUMBER3 WORK_APPDTE1    WORKNUMBER2 WORK_APPDTE2    WORKNUMBER3 WORK_APPDTE3    WORKNUMBER1 MOBILE_APPDTE1  MOBILENUMBER1   MOBILE_APPDTE2  MOBILENUMBER2   MOBILE_APPDTE3  MOBILENUMBER3
23  2016-11-25  111111111   2015-06-22  222222222   2015-02-22  333333333   2017-01-25  444444444   2016-02-12  555555555   2015-06-13  666666666   2017-05-18  777777777   2016-12-23  888888888   2016-01-11  999999999

but I am after

ID  Type    Date_Changed    Tel_Number  Rank
23  Home    2016-11-25  111111111   1
23  Home    2015-06-22  222222222   2
23  Home    2015-02-22  333333333   3
23  Work    2017-01-25  444444444   1
23  Work    2016-02-12  555555555   2
23  Work    2015-06-13  666666666   3
23  Mobile  2017-05-18  777777777   1
23  Mobile  2016-12-23  888888888   2
23  Mobile  2016-01-11  999999999   3

Many thanks in advance of help

Solution

  • Assuming you don't need dynamic, yet another option is using a CROSS APPLY.

    Also, assuming you didn't need a dynamic solution.

    Example

    Select A.ID
      ,B.*
 From  YourTable A
 Cross Apply ( values ('Home'  ,HOME_APPDTE1  ,HOMENUMBER1  ,1)
                     ,('Home'  ,HOME_APPDTE2  ,HOMENUMBER2  ,2)
                     ,('Home'  ,HOME_APPDTE3  ,HOMENUMBER3  ,3)
                     ,('Work'  ,WORK_APPDTE1  ,WORKNUMBER1  ,1)
                     ,('Work'  ,WORK_APPDTE2  ,WORKNUMBER2  ,2)
                     ,('Work'  ,WORK_APPDTE3  ,WORKNUMBER2  ,3)
                     ,('Mobile',MOBILE_APPDTE1,MOBILENUMBER1,1)
                     ,('Mobile',MOBILE_APPDTE2,MOBILENUMBER2,2)
                     ,('Mobile',MOBILE_APPDTE3,MOBILENUMBER3,3)
             ) B ([Type],[DateChanged],[Tel_Number],[Rank])