Multiple column to single column with column header?

Question

I have table like following. Staging table:

+---------+------+--------+------+
|   Id    | I1   | I2     | I3   | 
+---------+------+--------+------+
|    1    | x1   | 1      |  2   |
|    2    | C    | A      |  B   |
|    3    | x7   | x8     |  X9  |
+---------+------+--------+------+

I'm using following query to pivot this table:

select    ID
,value
from dbo.Staging
unpivot
(
  value
  for col in (I1, I2,I3 )     
) un

I want "Column header name" from the staging table also like following. How can I do this?

+-------+-------+-------------------------+
| value | ID    |  Column header name     |
+-------+-------+-------------------------+
|  x1   |  1    |       I1                |
|  1    |  1    |       I2                |
|  2    |  1    |       I3                |
|  C    |  2    |       I1                |
|  A    |  2    |       I2                |
|  B    |  2    |       I3                | 
|  x7   |  3    |       I1                |
|  x8   |  3    |       I2                | 
|  x9   |  3    |       I3                |
+-------+-------+-------------------------+

Answer 1

Assuming you have a fixed number of columns which you know beforehand (3 in this case), and that all values are non-null, you could hard-code the column names into your output, like so:

;with cte as
 (select 
  ID,
  value,
  row_number() over (partition by id order by value) rn
  from dbo.Staging
  unpivot
  (
   value
   for col in (I1, I2,I3 )     
  ) un
 )

 select 
 id, 
 value, 
 case when rn = 1 then 'I1' when rn = 2 then 'I2' else 'I3' end as 'Column header name'
 from cte

EDIT: Since the values might not be in order always, we can use ID with ORDER BY in the OVER clause. This is effectively a dummy value passed for sorting, so that it will always return the data as is without actually sorting.