CODEWITHSUNDEEP
sql-servert-sqlpivot
eKKiM
eKKiM

Reputation: 431

SQL PIVOT on a variable amount of rows

I have the following data on a MSSQL Server:

Item No_    Unit Of Measure     Qty Per Base UoM    Rounding Precision
000001      PIECE               1                   1.0
000001      PALLET              100                 1.0
000001      BOX                 12                  1.0
000002      KG                  1                   1.0
000002      TON                 1000                0.001

I am trying to get the following output:

Item No_    UoM1    Qty pb UoM1 RP1     UoM2    Qty pb UoM2     RP2     UoM3    Qty pb UoM3 RP3
000001      PIECE   1           1.0     PALLET  100             1.0     BOX     12          1.0
000002      KG      1           1.0     TON     1000            0.0001  NULL    NULL        NULL

I tried achieving this using the PIVOT operator but it does not seem te be correct.

What is the correct way to achieve my desired output?

Upvotes: 3

Views: 57

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175796

For trully generic solution you need to use Dynamic-SQL. But you say you want starting point, so I give you one. You can use:

LiveDemo

WITH cte AS
(
   SELECT *, ROW_NUMBER() OVER(PARTITION BY Item_No_ ORDER BY (SELECT 1)) AS rn
   FROM #mytable
)
select Item_No_,
  max(case when rn = 1 then Unit_Of_Measure end) UoM1,
  max(case when rn = 1 then Qty_Per_Base_UoM end) [Qty pb UoM1],
  max(case when rn = 1 then Rounding_Precision end) RP1,
  max(case when rn = 2 then Unit_Of_Measure end) UoM2,
  max(case when rn = 2 then Qty_Per_Base_UoM end) [Qty pb UoM2],
  max(case when rn = 2 then Rounding_Precision end) RP2,
  max(case when rn = 3 then Unit_Of_Measure end) UoM3,
  max(case when rn = 3 then Qty_Per_Base_UoM end) [Qty pb UoM3],
  max(case when rn = 3 then Rounding_Precision end) RP3,
  max(case when rn = 4 then Unit_Of_Measure end) UoM4,
  max(case when rn = 4 then Qty_Per_Base_UoM end) [Qty pb UoM4],
  max(case when rn = 4 then Rounding_Precision end) RP4
from cte
group by Item_No_;

The point is that if your number of units is known in advance you can create hardcoded columns from 1 .. n.

For more info search Dynamic Pivot multiple columns. It is achievable, but first try to crack this solution.

Upvotes: 4

Related Questions