Unpivot a table but add an index column to number each element in the new column based on it's group

Question

I have a table like this

+--+------+------+------+------+------+
|ID|Value1|Value2|Value3|Value4|Value5|
+--+------+------+------+------+------+
|1 |100   |105   |110   |134   |102   |
|2 |96    |45    |103   |100   |105   |
|3 |40    |85    |194   |134   |135   |
+--+------+------+------+------+------+

which I can unpivot similar to this

SELECT
  ID,
  Value
FROM
(
  SELECT
    ID,
    Value1,
    Value2,
    Value3,
    Value4,
    Value5
  FROM
    Tbl
) as src
UNPIVOT
(
  Value for Values in (Value5, Value4, Value3, Value2, Value1)
) as up

and get a result that looks like this

+--+-----+
|ID|Value|
+--+-----+
|1 |102  |
|1 |134  |
|1 |110  |
|1 |105  |
|1 |100  |
|2 |105  |
|2 |100  |
|2 |103  |
|2 |45   |
|2 |96   |
|3 |135  |
|3 |134  |
|3 |194  |
|3 |85   |
|3 |40   |
+--+-----+

How can I go about adding an additional column to the result that numbers each item in the unpivot. I need to transform our data into this format for an extract, and the indexing is used to set other values. Unpivoting is new to me, I imagine it needs to be done in that block somehow as that is where the ordering exists - I've explicitly stated to unpivot in the reverse column order. There is no ordering otherwise among the rest of the columns to use an ORDER BY.

+--+-----+-----+
|ID|Value|Index|
+--+-----+-----+
|1 |102  |5    |
|1 |134  |4    |
|1 |110  |3    |
|1 |105  |2    |
|1 |100  |1    |
|2 |105  |5    |
|2 |100  |4    |
|2 |103  |3    |
|2 |45   |2    |
|2 |96   |1    |
|3 |135  |5    |
|3 |134  |4    |
|3 |194  |3    |
|3 |85   |2    |
|3 |40   |1    |
+--+-----+-----+

Possible duplicate of [Unpivot with column name](https://stackoverflow.com/questions/19055902/unpivot-with-column-name) — Tab Alleman, Aug 29 '17 at 13:56

score 2 · Accepted Answer · answered Aug 29 '17 at 14:01

2

Figured it out via section 3 on Values and using a CROSS APPLY

SELECT
  t.*
FROM
  tbl
CROSS APPLY (
  VALUES
    (ID, Value5, '5'),
    (ID, Value4, '4'),
    (ID, Value3, '3'),
    (ID, Value2, '2'),
    (ID, Value1, '1')
) t(ID, Value, Index)

answered Aug 29 '17 at 14:01

TomNash

3,147
2
21
57

score 0 · Answer 2 · answered Aug 29 '17 at 14:05

i'm unsure about how the engine will determine the order in which to assign row number. I'm assuming it's the order of the vals defined in the unpivot...

DEMO: http://rextester.com/WTKLDR72234

SELECT ID
     , val
     , 6-row_number() over (partition by ID order by (Select null)) as Idx
FROM (SELECT ID
           , Value1
           , Value2
           , Value3
           , Value4
           , Value5
      FROM Tbl) as src
UNPIVOT
(
  val for vals in (Value5, Value4, Value3, Value2, Value1)
) as up;

Unpivot a table but add an index column to number each element in the new column based on it's group

2 Answers2