Clash Royale CLAN TAG#URR8PPP

Row to column in SQL Server with dynamic number of rows

My source data looks something like this

ID Phone Type Phone Number
-------------------------------------
308820 P 8136542273
308820 F 8136541384
308820 P 8139108555
308820 P 8136842229
308820 F 8139108655
211111 P 6874598695
456788 F 5687659867


In the above data, Phone type = P is phone and F is fax.

Phone type = P

F

I need to sort the data and then pick only one F type and one P type phone number and populate the data as below

ID Fax Number Phone Number
-------------------------------------
308820 8136541384 8136542273
211111 6874598695
456788 5687659867


Can someone help me out how to achieve this. There can be n number of Phone Numbers and Fax numbers against one ID but I need to sort it and pick the first.

4 Answers
4

You can use conditional aggregation:

select id, max(case when phone_type = 'F' then phone_number end) as fax,
max(case when phone_type = 'P' then phone_number end) as phone
from t
group by id;


you can use aggregate function and sub-query

select id,max(fax_Number) as fax_Number, max(phone_Number) as phone_Number
from
(select id, case when phone_type = 'F' then phone_number end as fax_Number,
case when phone_type = 'P' then phone_number end as phone_Number
from yourtable
) as t group by id


If the requirement is to pick first value and not the max value then you can use it.

; with CTE1
As
(
select row_number() over (partition by id,phonetype order by ID) as Num,ID,
case when phonetype = 'F' then PhoneNumber end as FaxNumber,
case when phonetype = 'P' then PhoneNumber end as PhoneNumber
from ContactDetails
)
select ID,max(FaxNumber),max(PhoneNumber)
from (select ID,FaxNumber,PhoneNumber from CTE1 where num = 1)as T2
group by ID


Guys thanks for the response. The query which I created is below. The help I got above was used in my query below. However the problem is that in the Binary_CheckSum it randomly picks up a Phone number and as such again selects the same record.

What I am trying to find is that for a particular ID and AddressID combination has the iSActive flag or Phone Number or Fax number has changed so that I need to select that record and insert it in PrescriberLocation table.

select TESTID, ADDRESSID, max(PhoneNumber) as PhoneNumber,max(FaxNumber) as FaxNumber,'1' as isactive from (select distinct a.TESTID
, b.AddressId , CASE WHEN c.PhoneType = 'P'THEN C.PhoneNumber END PhoneNumber
, CASE WHEN c.PhoneType = 'F'THEN C.PhoneNumber END FaxNumber
,'1' as isactive from stg_Address a inner join stg_AddressPhone c on a.TESTID = c.TESTID and a.AddressID = c.AddressID INNER join pbmsys_new.dbo.sc_Address b on
upper(a.Address1) = upper(b.Address1) and upper(isnull(a.Address2,'')) = upper(b.Address2) join pbmsys_new.[dbo].[dr_PrescriberLocation] d
on a.TESTID = d.TESTID and b.AddressId = d.AddressId
where BINARY_CHECKSUM(1,c.PhoneNumber, FaxNumber) != BINARY_CHECKSUM(d.IsActive,d.PhoneNumber,d.FaxNumber) and d.PrescriberLocationId = SELECT max(Z.PrescriberLocationId) as PrescriberLocationId FROM pbmsys_new.dbo.dr_PrescriberLocation Z where d.TESTID = z.TESTID and d.AddressId =z.AddressId))f group by TESTID, AddressId


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.