Oracle pivot transform to SQL server

You might find it odd but as per my knowledge this is how we can do it in SQL server.

We need to create another dimension for the same pivot columns by appending anything to it and put the same into PIVOT clause.

As you see I have added 1 and create another column to be used for locvrc

SELECT *
   FROM (SELECT d.loc_area,
                d.loc_rack,
                d.loc_height,
                d.loc_place,
                d.locvrc,
                row_number() OVER(PARTITION BY d.loc_area, d.loc_rack, d.loc_place
                                   ORDER BY d.loc_height) rn_pivot,
                cast(row_number() OVER(PARTITION BY d.loc_area, d.loc_rack, d.loc_place
                                   ORDER BY d.loc_height) as varchar) + '1' rn_pivot_locvrc
           FROM (select locvrc,
                        SUBSTRing(stoloc, 0, 2) loc_area,
                        SUBSTRing(stoloc, 4, 2) loc_rack,
                        SUBSTRing(stoloc, 6, 1) loc_place,
                        right(stoloc, 2) loc_height
                   FROM locls) d) A
  PIVOT (MAX(loc_height) FOR rn_pivot IN ([1],[2],[3],[4])) P1
  PIVOT (MAX(locvrc) FOR rn_pivot_locvrc IN ([11],[21],[31],[41])) P2

P.S. My intention is to tell how you can PIVOT multiple columns abut unfortunately I certainly cannot validate the result set.

EDIT:- Updated query with Op's actual data

SELECT loc_area,loc_rack,loc_place
      ,max([1]) as height_1
      ,max([11]) as unique_1
      ,max([2]) as height_2
      ,max([21]) as unique_2
      ,max([3]) as height_3
      ,max([31]) as unique_3
      ,max([4]) as height_4
      ,max([41]) as unique_4
   FROM (SELECT d.loc_area,
                d.loc_rack,
                d.loc_height,
                d.loc_place,
                d.locvrc,
                row_number() OVER(PARTITION BY d.loc_area, d.loc_rack, d.loc_place
                                   ORDER BY d.loc_height) rn_pivot,
                cast(row_number() OVER(PARTITION BY d.loc_area, d.loc_rack, d.loc_place
                                   ORDER BY d.loc_height) as varchar) + '1' rn_pivot_locvrc
           FROM (select locvrc,
                        SUBSTRing(stoloc, 0, 2) loc_area,
                        SUBSTRing(stoloc, 4, 2) loc_rack,
                        SUBSTRing(stoloc, 6, 1) loc_place,
                        right(stoloc, 2) loc_height
                   FROM locls) d) A
  PIVOT (MAX(loc_height) FOR rn_pivot IN ([1],[2],[3],[4])) P1
  PIVOT (MAX(locvrc) FOR rn_pivot_locvrc IN ([11],[21],[31],[41])) P2
group by loc_area,loc_rack,loc_place

OR

Using aggregation and case also it can be done,

SELECT loc_area,loc_rack,loc_place
      ,max(case when rn = 1 then loc_height end) height_1
      ,max(case when rn = 1 then locvrc end) unique_1
      ,max(case when rn = 2 then loc_height end) height_2
      ,max(case when rn = 2 then locvrc end) unique_2
      ,max(case when rn = 3 then loc_height end) height_3
      ,max(case when rn = 3 then locvrc end) unique_3
      ,max(case when rn = 4 then loc_height end) height_4
      ,max(case when rn = 4 then locvrc end) unique_4
   FROM (SELECT d.loc_area,
                d.loc_rack,
                d.loc_height,
                d.loc_place,
                d.locvrc,
                row_number() OVER(PARTITION BY d.loc_area, d.loc_rack, d.loc_place
                                   ORDER BY d.loc_height) rn
           FROM (select locvrc,
                        SUBSTRing(stoloc, 0, 2) loc_area,
                        SUBSTRing(stoloc, 4, 2) loc_rack,
                        SUBSTRing(stoloc, 6, 1) loc_place,
                        right(stoloc, 2) loc_height
                   FROM locls) d) A
group by loc_area,loc_rack,loc_place;