mercredi 2 décembre 2015

How to use two pivot

I have 3 Tables 1) user 2) phone 3) address

1) user

 id    name
 1     abc
 1     abc
 2      tyu
 2      tyu

2) phone

    id     number
     1     0987654
     1     0890764
     2     3445667
     2     5643456

3) address

id     addr  type
 1    usa    1
 1    uae    1 
 2    Uk     2

So now i have written below query:

select * from (
              select u.id, u.name , p.number, cs.COL + CAST(row_number()over(PARTITION BY u.ID ORDER BY cs.COL) AS VARCHAR) RN ,
              cs1.COL + CAST(row_number()over(PARTITION BY a.IDnum ORDER BY cs1.COL) AS VARCHAR) RN1
               ,a.addr1
              from user u left join phone p on p.id = u.id
              left join address as a on a.id = p.id CROSS APPLY (VALUES ('phone',number)) CS(Col,val)
                                                          CROSS APPLY (VALUES ('add',a.addr)) CS1(Col,val)
              where u.id=1 and a.type = '1'
              )P
    PIVOT (MAX(number) FOR RN IN ([phone1],[phone2])) as pivot1
    PIVOT (MAX(addr1)  FOR RN1 IN ([add1],[add2])) as pivot2

so the above query give me output like:

id  name    phone1      phone2      add1        add2
1   abc     NULL        0987654     NULL       usa
1   abc     0890764      NULL       uae         NULL   

But i want the ouput like below:

  id    name    phone1      phone2      add1        add2
    1   abc     0890764     0987654     uae         usa

So how can i achieve this with the pivot ?

Aucun commentaire:

Enregistrer un commentaire