How to retain index when unpivoting data (SQL Server)

Question

I am trying to unpivot a table to EAV format, but would like to retain some extra meta data with each row.

Initial Table

| Brand | Name     | Number | Col1 | Col2 | 
|-------|----------|--------|------|------|
| A     | Book     | #1     | 1    | 2    | 
| B     | Magazine | #2     | 1    | 2    | 

Desired Output

| Number | key   | val      | 
|--------|-------|----------|
| #1     | Brand | A        | 
| #1     | Name  | Book     | 
| #1     | Col1  | 1        |
| #1     | Col2  | 2        |
| #2     | Brand | B        |
| #2     | Name  | Magazine |
| #2     | Col1  | 1        |
| #2     | Col2  | 2        |

Actual Output

Invalid column name 'Number'.

Example Query

select 
[Number], -- How can this be selected?
[key],
[val]
from (
  select
  [Number],
  [Brand],
  [Name]
  from [SomeTable]
) data
unpivot (
  [val]
  for [key] in (
    [Brand],
    [Name],
    [Number],
    [Col1],
    [Col2]
  )
) as unpiv

How can I select a key value pair as well as the corresponding number as an index?

Answer 1

You can use a CROSS APPLY in concert with a little XML

If 2016+, there is a JSON approach which is a nudge more performant

Example

Declare @YourTable Table ([Brand] varchar(50),[Name] varchar(50),[Number] varchar(50),[Col1] int,[Col2] int)
Insert Into @YourTable Values 
 ('A','Book','#1',1,2)
,('B','Magazine','#2',1,2)

Select A.Number
      ,C.*
 From  @YourTable A
 Cross Apply ( values ( convert(xml,(select a.* for xml raw ) ) ) ) B(XMLData)
 Cross Apply ( 
                Select [key] = xAttr.value('local-name(.)', 'varchar(100)')
                      ,Value = xAttr.value('.','varchar(max)')
                 From  B.XMLData.nodes('//@*') xNode(xAttr)
                 Where xAttr.value('local-name(.)', 'varchar(100)') not in ('Number')
             ) C 

Returns

Number  key    value
#1      Brand   A
#1      Name    Book
#1      Col1    1
#1      Col2    2
#2      Brand   B
#2      Name    Magazine
#2      Col1    1
#2      Col2    2

EDIT - Added JSON version if 2016+

Select A.Number
      ,B.*
 From  @YourTable A
 Cross Apply ( 
                Select [Key]
                      ,Value
                 From OpenJson((Select A.* For JSON Path,Without_Array_Wrapper )) 
                 Where [Key] not in ('Number')
             ) B