CODEWITHSUNDEEP
sqlsql-serverunpivot
Casperonian
Casperonian

Reputation: 184

Unpivot SQL statement

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

Upvotes: 0

Views: 256

Answers (3)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

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 |

Upvotes: 2

adrianm
adrianm

Reputation: 14726

I am always confused by the UNPIVOT syntax so I prefer CROSS APPLY/VALUES instead

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

Upvotes: 1

Angel_Boy
Angel_Boy

Reputation: 1028

The result set that you get when you run the below query,It does not contain AssyLotNo column:

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

Therefore when you try selecting a column that does not exist in the result set, you get an error which tells you that it does not exist!!

Upvotes: 0

Related Questions