sql server语言 - 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

上述查询的目的是将 AssyLotNo 字段也包含在显示和值列中。所有字段都存在于表中eMap_Data，但是，我一直得到字段 AssyLotNo 的无效列名，该字段显然存在于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 架构设置：

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')

查询 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)

结果：

| 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 2

我总是对 UNPIVOT 语法感到困惑，所以我更喜欢交叉应用/值

SELECT AssyLotNo
      ,AltLotName
      ,WfrNoDisplay
      ,CA1.Display
      ,CA1.[Value]
FROM eMap_Data
     CROSS APPLY (
          SELECT *
          FROM (VALUES ('ALotNumber', ALotNumber)
                      ,('WfrNumber', WfrNumber)
                      ,('NeedSpeedInfo', NeedSpeedInfo)
                      ,...
                      ,('AssyLotNo', AssyLotNo)
               ) AS X(Display, [Value])
     ) AS CA1

Answer 3

运行

以下查询时得到的结果集，它不包含 AssyLotNo 列：

Select * FROM
eMap_Data
UNPIVOT 
(Value for Display in 
(AltLotNumber, WfrNumber, NeedSpeedInfo, MapType, BuildList, [Type], ProductGrade,    AssyLotNo)
) as Param

因此，当您尝试选择结果集中不存在的列时，您会收到一个错误，告诉您它不存在！！