Oracle pivot transform to SQL server

Question

Short question, not so familiar with SQL server, but I have query with multiple aggregate in oracle sql server.

What is sql server equivalent query for multiple aggregate?

Oracle code:

[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
           FROM (select locvrc,
                        SUBSTR(stoloc, 0, 2) loc_area,
                        SUBSTR(stoloc, 4, 2) loc_rack,
                        SUBSTR(stoloc, 6, 1) loc_place,
                        SUBSTR(stoloc, -2) loc_height
                   FROM locls) d)
  PIVOT (MAX(loc_height) AS EXT, MIN(locvrc) AS UNIQ FOR rn_pivot IN ('1' AS HEIGHT_1, '2' AS HEIGHT_2, '3' AS HEIGHT_3, '4' AS HEIGHT_4))
  ORDER BY LOC_AREA,
        LOC_RACK,
        LOC_PLACE]

SQL Server:

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
           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 

This is working fine, but I need the other aggregate |(MIN(locvrc)| also

Thank you in advance!

Br Jan

/Edit,

Original table:

 Stoloc          locvrc
 5D-32A-00       699787
 5D-32A-10       439567
 5D-32A-20       429456
 5D-32A-30       122172
 5D-32B-00       328311
 5D-32B-10       247422
 5D-32B-20       133244
 5D-32B-30       464978

Table after substring:

loc_area loc_rack loc_place loc_height locvrc
 5D         32       A        00       699787
 5D         32       A        10       439567
 5D         32       A        20       429456
 5D         32       A        30       122172
 5D         32       B        00       328311
 5D         32       B        10       247422
 5D         32       B        20       133244
 5D         32       B        30       464978

What I have with Oracle is this:

loc_area loc_rack loc_place Height1 Uniq1   Height2 Uniq2   Height3 Uniq3 Height4 Unique4
5D       32        A         00     699787    10    439567    20    429456    30   122172
5D       32        B         00     328311    10    247422    20    133244    30   464978

So from the original table the stoloc will be cutted to different columns. After this in one line would be area, rack, place and with them in one row Height and locvrc. I have completed this in Oracle database, but dont know how to in SQl server. Doesnt need to be this kind of solution, any will do.

Answer 1

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;