; with numbered as
(
  select *,
      -- Assign number to each record
      -- Numbering is separate for each pair of MLISNPI and CLIENT_ID1
      -- Highest [Sum of desc] will get number 1 and so forth
         Rank() over (partition by [MLISNPI]
                            order by [Sum of count] desc) rn
    from [TargettingReport]
),
ctemain as (
select [MLISNPI],
       [CLIENT_ID1],
       [Sum of count]
  from numbered
)


select * from ctemain

+----------+------------+--------------+
| MLISNPI  | CLIENT_ID1 | Sum of count |
+----------+------------+--------------+
| 03001363 |     101522 |          436 |
| 03001363 |     101596 |            0 |
| 03001363 |     101597 |            0 |
| 03002312 |     102165 |           66 |
| 03002742 |     100062 |            1 |
| 03002742 |     211074 |         1229 |
| 03006958 |     102235 |           21 |
| 03014986 |     213926 |            5 |
| 03016270 |     213143 |            3 |
| 03023284 |     212876 |           44 |
| 03023284 |     213801 |           55 |
| 03023821 |     100218 |            0 |
| 03028812 |     211144 |          133 |
| 03041666 |     100236 |          346 |
| 03041666 |     103164 |           65 |
| 03051731 |     213402 |          157 |
| 03058777 |     100572 |           28 |
| 03065509 |     101632 |           29 |
| 03071952 |     213632 |            6 |
| 03072059 |     101506 |            4 |
| 03081449 |     100087 |          398 |
| 03083205 |     214311 |            7 |
| 03117698 |     210178 |          203 |
| 03121302 |     214008 |            9 |
| 03139502 |     102179 |         1635 |
| 03147455 |     216022 |           21 |
| 03149204 |     211425 |            1 |
| 03186883 |     215748 |            1 |
| 03186883 |     215749 |           10 |
| 03190331 |     212289 |           26 |
| 03800285 |     101108 |         8052 |
| 03800285 |     101596 |            0 |
| 03800285 |     101597 |            0 |
| 03800350 |     212419 |            9 |
| 03800616 |     110461 |            0 |
| 03800616 |     213456 |            3 |
| 03802018 |     103136 |           32 |
| 03803412 |     201257 |            3 |
+----------+------------+--------------+

+----------+-----------+-------------+------------+-------------+-------------+----------+
| MLISNPI  | clientid1 | sumofcount1 | clientid 2 | sumofcount2 | client id 3 | sum of 3 |
+----------+-----------+-------------+------------+-------------+-------------+----------+
| 03001363 |    101522 |         436 |     101596 |           0 |      101597 |        0 |
| 03002312 |    102165 |          66 |            |             |             |          |
| 03002742 |    100062 |           1 |     211074 |        1229 |             |          |
| 03006958 |    102235 |          21 |            |             |             |          |
| 03014986 |    213926 |           5 |            |             |             |          |
| 03016270 |    213143 |           3 |            |             |             |          |
| 03023284 |    212876 |          44 |     213801 |          55 |             |          |
+----------+-----------+-------------+------------+-------------+-------------+----------+

CREATE TABLE [dbo].[TargettingReport](
    [MLISNPI] [varchar](50) NULL,
    [IMS_PRESCRIBER_ID] [varchar](50) NULL,
    [CLIENT_ID1] [varchar](50) NULL,
    [Sum of count] [int] NULL
) ON [PRIMARY]

select *
from
(
  select MLISNPI,
    cast(value as int) value,
    case col
      when 'client_id1' then 'clientid_' + cast(rn as varchar(10))
      when 'SumofCount' then 'SumofCount_' + cast(rn as varchar(10))
    end col
  from
  (
    select MLISNPI, Client_id1, cast(SumOfCount as varchar(50)) SumofCount,
      row_number() over(partition by MLISNPI order by Client_id1) rn
    from TargettingReport
  ) x
  unpivot
  (
    value
    for col in (client_id1, sumofcount)
  ) u
) x1
pivot
(
  sum(value)
  for col in ([clientid_1], [SumofCount_1],
              [clientid_2], [SumofCount_2],
              [clientid_3], [SumofCount_3])
) p

DECLARE @colsPivot AS NVARCHAR(MAX),
    @colsUnpivot AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

SET @colsUnPivot
  = stuff((select ','
           +quotename(C.name )
         from sys.columns as C
         where C.object_id = object_id('TargettingReport') and
               C.name IN ('CLIENT_ID1', 'SumOfCount')
         for xml path('')), 1, 1, '')

SET @colsPivot
  = stuff((select ','+quotename(case when C.name = 'client_id1' then 'clientid' else c.name end + '_' + cast(rn as varchar(10)))
         from sys.columns as C
         cross apply
         (
           select row_number() over(partition by MLISNPI order by Client_id1) rn
            from TargettingReport
          ) x
         where C.object_id = object_id('TargettingReport') and
               C.name IN ('CLIENT_ID1', 'SumOfCount')
         group by name, rn
         order by rn
         for xml path('')), 1, 1, '')

set @query
  = '
      select *
      from
      (
        select MLISNPI,
          cast(value as int) value,
           case col
            when ''client_id1'' then ''clientid_'' + cast(rn as varchar(10))
            when ''SumofCount'' then ''SumofCount_'' + cast(rn as varchar(10))
          end col
        from
        (
          select MLISNPI, Client_id1, cast(SumOfCount as varchar(50)) SumofCount,
            row_number() over(partition by MLISNPI order by Client_id1) rn
          from TargettingReport
        ) x1
        unpivot
        (
           value
           for col in (' + @colsUnPivot + ')
        ) unpvt

      ) x2
      pivot
      (
        sum(value)
        for col in(' + @colsPivot +')
      )p'

exec(@query)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(wagecode)
                  FROM t1
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'')

set @query = 'SELECT empid, ' + @cols + ' from
             (
                 select empid, wagecode, amount
                 from t1
            ) x
            pivot
            (
                sum(amount)
                for wagecode in (' + @cols + ')
            ) p '

execute(@query)