Unpivot SQL statement

Question

SELECT 
  AssyLotNo, 
  AltLotName, 
  WfrNoDisplay, 
  Param.Display, 
  Param.Value
FROM
  eMap_Data
UNPIVOT 
(Value for Display in 
  (AltLotNumber, WfrNumber, NeedSpeedInfo, MapType, BuildList, Type, ProductGrade,  AssyLotNo)
) as Param

Objective of the query above is to have the AssyLotNo field to also be included in the display and value columns. All fields exists in the table eMap_Data, however, I keep getting invalid column name for the field AssyLotNo which clearly exists in eMap_Data.

eMap_Data table fields
AssyLotNo | AltLotName | AltLotNumber | WfrNoDisplay | WfrNumber | NeedSpeedInfo | MapType | BuildList     | Type | ProductGrade

Desired output is as follow:
AssyLotNo AltLotName WfrNoDisplay Display        Value
------------------------------------------------------------------
ABC1231   ABC123     01           AltLotName     ABC123
ABC1231   ABC123     01           WfrNumber      01
ABC1231   ABC123     01           NeedSpeedInfo  Y
ABC1231   ABC123     01           MapType        12
ABC1231   ABC123     01           BuildList      1,2,3,4,5,6
ABC1231   ABC123     01           Type           S
ABC1231   ABC123     01           ProductGrade   C
ABC1231   ABC123     01           AssyLotNo      ABC1231

Answer 1

SQL Fiddle

MS SQL Server 2012 Schema Setup:

create table eMap_Data
(
  WfrNoDisplay   varchar(20),
  AltLotName     varchar(20),  
  WfrNumber      varchar(20),
  NeedSpeedInfo  varchar(20),
  MapType        varchar(20),
  BuildList      varchar(20),
  Type           varchar(20),
  ProductGrade   varchar(20),
  AssyLotNo      varchar(20)
)


insert into eMap_Data values
('01', 'ABC123', '01','Y','12','1,2,3,4,5,6','S','C','ABC1231')

Query 1:

select E.AssyLotNo,
       E.AltLotName,
       E.WfrNoDisplay,
       T.Display,
       T.Value
from eMap_Data as E
  cross apply (values(AltLotNAme,    'AltLotNAme'),
                     (WfrNumber,     'WfrNumber'), 
                     (NeedSpeedInfo, 'NeedSpeedInfo'),
                     (MapType,       'MapType'), 
                     (BuildList,     'BuildList'), 
                     (Type,          'Type'), 
                     (ProductGrade,  'ProductGrade'), 
                     (AssyLotNo,     'AssyLotNo')
              ) as T(Value, Display)

Results:

| ASSYLOTNO | ALTLOTNAME | WFRNODISPLAY |       DISPLAY |       VALUE |
|-----------|------------|--------------|---------------|-------------|
|   ABC1231 |     ABC123 |           01 |    AltLotNAme |      ABC123 |
|   ABC1231 |     ABC123 |           01 |     WfrNumber |          01 |
|   ABC1231 |     ABC123 |           01 | NeedSpeedInfo |           Y |
|   ABC1231 |     ABC123 |           01 |       MapType |          12 |
|   ABC1231 |     ABC123 |           01 |     BuildList | 1,2,3,4,5,6 |
|   ABC1231 |     ABC123 |           01 |          Type |           S |
|   ABC1231 |     ABC123 |           01 |  ProductGrade |           C |
|   ABC1231 |     ABC123 |           01 |     AssyLotNo |     ABC1231 |