Columns having nulls are not shown when a row is unpivoted

Question

I have come to a rather strange sort of an issue. I have a row as follows in SQL Server:

Row number  ITEMTYPE    LIFECYCLEPHASE  DESCRIPTION ITEMCATEGORY    size    PRODUCTLINES    REV REVINCORPDATE   REVRELEASEDATE  EFFECTIVITYDATE SHIPPABLEITEM   EXCLUDEFROMROLLUP   COMPLIANCECALCULATEDDATE    OVERALLCOMPLIANCE   ITEMGROUPS  MODELREF    COMMODITY   list07  text13  text12  text07  text01  CREATEUSER  date01  list10  list09  list13  list24  text17  text24  text25  multiList01 multiList04 list17  numeric01   list03  list06  list25  list14  numeric03   numeric02   text21  list15  text11  text19  text20  date02  multiList02 list01  multiList03 list19  list20  text02  multiList05 list21  list22  text23  text03  text04  text05  money02 list12  list16  list11  list18
1   .6C12PXMZ   Fastener, Purchased Obsolete    SCREW,PAN, PHI M6X12    Normal MRP Purchased Part   PC  NS-MISC X1 GOLIVE_0001  NULL    2007-04-25 06:00:00.0000000 2007-04-25 06:00:00.0000000 No  0   NULL    NULL    NULL    NULL    Fastener, Purchased No  NULL    NULL    0   NULL    Fallon, Dermot(**dfallon)   NULL    NULL    0369    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NS-MISC NULL    RA  RA  2-MS1-7A    NULL    NULL    NULL    NULL    NULL    0   0   NULL    NULL    NULL    NULL    NULL

When I unpivot this row. It should show 66 rows and 3 columns, if the below query is used to unpivot above mentioned row.

Select  ROW
 ,ColumnName
       , ColumnValue
       --INTO  ##TITLE_BLOCK_PARTS_T 
FROM 
(
    Select
    ROW,
    CAST([number] AS VARCHAR(8000))AS number,

CAST([ITEMTYPE] AS VARCHAR(8000))AS ITEMTYPE,

CAST([LIFECYCLEPHASE] AS VARCHAR(8000))AS LIFECYCLEPHASE,

CAST([DESCRIPTION] AS VARCHAR(8000))AS DESCRIPTION,

CAST([ITEMCATEGORY] AS VARCHAR(8000))AS ITEMCATEGORY,

CAST([size] AS VARCHAR(8000))AS size,

CAST([PRODUCTLINES] AS VARCHAR(8000))AS PRODUCTLINES,

CAST([REV] AS VARCHAR(8000))AS REV,

CAST([REVINCORPDATE] AS VARCHAR(8000))AS REVINCORPDATE,

CAST([REVRELEASEDATE] AS VARCHAR(8000))AS REVRELEASEDATE,

CAST([EFFECTIVITYDATE] AS VARCHAR(8000))AS EFFECTIVITYDATE,

CAST([SHIPPABLEITEM] AS VARCHAR(8000))AS SHIPPABLEITEM,

CAST([EXCLUDEFROMROLLUP] AS VARCHAR(8000))AS EXCLUDEFROMROLLUP,

CAST([COMPLIANCECALCULATEDDATE] AS VARCHAR(8000))AS COMPLIANCECALCULATEDDATE,

CAST([OVERALLCOMPLIANCE] AS VARCHAR(8000))AS OVERALLCOMPLIANCE,

CAST([ITEMGROUPS] AS VARCHAR(8000))AS ITEMGROUPS,

CAST([MODELREF] AS VARCHAR(8000))AS MODELREF,

CAST([COMMODITY] AS VARCHAR(8000))AS COMMODITY,

CAST([list07] AS VARCHAR(8000))AS list07,

CAST([text13] AS VARCHAR(8000))AS text13,

CAST([text12] AS VARCHAR(8000))AS text12,

CAST([text07] AS VARCHAR(8000))AS text07,

CAST([text01] AS VARCHAR(8000))AS text01,

CAST([CREATEUSER] AS VARCHAR(8000))AS CREATEUSER,

CAST([date01] AS VARCHAR(8000))AS date01,

CAST([list10] AS VARCHAR(8000))AS list10,

CAST([list09] AS VARCHAR(8000))AS list09,

CAST([list13] AS VARCHAR(8000))AS list13,

CAST([list24] AS VARCHAR(8000))AS list24,

CAST([text17] AS VARCHAR(8000))AS text17,

CAST([text24] AS VARCHAR(8000))AS text24,

CAST([text25] AS VARCHAR(8000))AS text25,

CAST([multiList01] AS VARCHAR(8000))AS multiList01,

CAST([multiList04] AS VARCHAR(8000))AS multiList04,

CAST([list17] AS VARCHAR(8000))AS list17,

CAST([numeric01] AS VARCHAR(8000))AS numeric01,

CAST([list03] AS VARCHAR(8000))AS list03,

CAST([list06] AS VARCHAR(8000))AS list06,

CAST([list25] AS VARCHAR(8000))AS list25,

CAST([list14] AS VARCHAR(8000))AS list14,

CAST([numeric03] AS VARCHAR(8000))AS numeric03,

CAST([numeric02] AS VARCHAR(8000))AS numeric02,

CAST([text21] AS VARCHAR(8000))AS text21,

CAST([list15] AS VARCHAR(8000))AS list15,

CAST([text11] AS VARCHAR(8000))AS text11,

CAST([text19] AS VARCHAR(8000))AS text19,

CAST([text20] AS VARCHAR(8000))AS text20,

CAST([date02] AS VARCHAR(8000))AS date02,

CAST([multiList02] AS VARCHAR(8000))AS multiList02,

CAST([list01] AS VARCHAR(8000))AS list01,

CAST([multiList03] AS VARCHAR(8000))AS multiList03,

CAST([list19 ] AS VARCHAR(8000))AS list19,

CAST([list20] AS VARCHAR(8000))AS list20,

CAST([text02] AS VARCHAR(8000))AS text02,

CAST([multiList05] AS VARCHAR(8000))AS multiList05,

CAST([list21] AS VARCHAR(8000))AS list21,

CAST([list22] AS VARCHAR(8000))AS list22,

CAST([text23] AS VARCHAR(8000))AS text23,

CAST([text03] AS VARCHAR(8000))AS text03,

CAST([text04] AS VARCHAR(8000))AS text04,

CAST([text05] AS VARCHAR(8000))AS text05,

CAST([money02] AS VARCHAR(8000))AS money02,

CAST([list12] AS VARCHAR(8000))AS list12,

CAST([list16] AS VARCHAR(8000))AS list16,

CAST([list11] AS VARCHAR(8000))AS list11,

CAST([list18] AS VARCHAR(8000))AS list18


   from 

 ##TITLE_BLOCK_PARTS_T_ORI

 )t  
   UNPIVOT (ColumnValue for ColumnName 
           IN (
           number,

ITEMTYPE,

LIFECYCLEPHASE,

DESCRIPTION,

ITEMCATEGORY,

size,

PRODUCTLINES,

REV,

REVINCORPDATE,

REVRELEASEDATE,

EFFECTIVITYDATE,

SHIPPABLEITEM,

EXCLUDEFROMROLLUP,

COMPLIANCECALCULATEDDATE,

OVERALLCOMPLIANCE,

ITEMGROUPS,

MODELREF,

COMMODITY,

list07,

text13,

text12,

text07,

text01,

CREATEUSER,

date01,

list10,

list09,

list13,

list24,

text17,

text24,

text25,

multiList01,

multiList04,

list17,

numeric01,

list03,

list06,

list25,

list14,

numeric03,

numeric02,

text21,

list15,

text11,

text19,

text20,

date02,

multiList02,

list01,

multiList03,

list19,

list20,

text02,

multiList05,

list21,

list22,

text23,

text03,

text04,

text05,

money02,

list12,

list16,

list11,

list18


               )
            )UP

I have tested above scenario in a 2012 instance and in 2008 instance. In 2012 the result set is as expected but somehow all the columns having null values are ignored in SQL Server 2008 instance.

Result set in 2012 is:

Row ColumnName  ColumnValue
1   number  000-0267-00
1   ITEMTYPE    Assemblies, General
1   LIFECYCLEPHASE  Obsolete
1   DESCRIPTION E1 ECHO CANCELLER
1   ITEMCATEGORY    Normal MRP Purchased Part
1   size    PC
1   PRODUCTLINES    NS-MISC
1   REV OB GOLIVE_0001
1   REVINCORPDATE   
1   REVRELEASEDATE  25-APR-07
1   EFFECTIVITYDATE 25-APR-07
1   SHIPPABLEITEM   No
1   EXCLUDEFROMROLLUP   0
1   COMPLIANCECALCULATEDDATE    Dec 30 1899 12:00AM
1   OVERALLCOMPLIANCE   0
1   ITEMGROUPS  
1   MODELREF    
1   COMMODITY   Assemblies, General
1   list07  No
1   text13  
1   text12  
1   text07  45
1   text01  
1   CREATEUSER  Fallon, Dermot(**dfallon)
1   date01  
1   list10  
1   list09  0369
1   list13  
1   list24  
1   text17  
1   text24  
1   text25  
1   multiList01 
1   multiList04 
1   list17  
1   numeric01   0
1   list03  
1   list06  
1   list25  
1   list14  
1   numeric03   0
1   numeric02   0
1   text21  
1   list15  
1   text11  
1   text19  
1   text20  
1   date02  
1   multiList02 
1   list01  NS-MISC
1   multiList03 NS
1   list19  RA
1   list20  RA
1   text02  2-MS1-7A
1   multiList05 Yes
1   list21  
1   list22  
1   text23  
1   text03  
1   text04  0
1   text05  2
1   money02 0
1   list12  
1   list16  
1   list11  
1   list18

Result set in 2008 is:

ROW ColumnName  ColumnValue
1   number  .6C12PXMZ
1   ITEMTYPE    Fastener, Purchased
1   LIFECYCLEPHASE  Obsolete
1   DESCRIPTION SCREW,PAN, PHI M6X12
1   ITEMCATEGORY    Normal MRP Purchased Part
1   size    PC
1   PRODUCTLINES    NS-MISC
1   REV X1 GOLIVE_0001
1   REVRELEASEDATE  2007-04-25 06:00:00.0000000
1   EFFECTIVITYDATE 2007-04-25 06:00:00.0000000
1   SHIPPABLEITEM   No
1   EXCLUDEFROMROLLUP   0
1   COMMODITY   Fastener, Purchased
1   list07  No
1   text07  0
1   CREATEUSER  Fallon, Dermot(**dfallon)
1   list09  0369
1   list01  NS-MISC
1   list19  RA
1   list20  RA
1   text02  2-MS1-7A
1   text04  0
1   text05  0

Can anybody tell me the reason behind such difference in both cases. I am totally lost on this one.

check these questions 1. http://stackoverflow.com/questions/1002989/sql-server-include-null-using-unpivot 2. http://stackoverflow.com/questions/23238232/handle-null-value-in-unpivot 3. http://dba.stackexchange.com/questions/117973/including-null-values-in-unpivoted-column — Pரதீப், Jun 29 '16 at 07:15
Then how come same behavior is not observed in 2012 instance. — Burhan Khalid Butt, Jun 29 '16 at 07:19

Columns having nulls are not shown when a row is unpivoted

0 Answers0