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