,
        [1940] int?,
        [1941] int?,
        [1942] int?,
        [1943] int?,
        [1944] int?,
        [1945] int?,
        [1946] int?,
        [1947] int?,     
        [1948] int?
      FROM  @"/Output/ReferenceGuide/DML/From/Pivot/Pivoted2.tsv"
      USING Extractors.Tsv(skipFirstNRows:1, nullEscape:"#null#");
    
    @result = 
      SELECT * 
      FROM @data
        UNPIVOT (Instrument FOR Band IN (
           [Beatles],
           [Rolling Stones],
           [Creedence Clearwater Revival],
           [Eagles],
           [Pink Floyd]
        )) AS UnpivotedTable1
        UNPIVOT (Children FOR YearOfBirth IN (
           [1940],
           [1941],
           [1942],
           [1943],
           [1944],
           [1945],
           [1946],
           [1947],
           [1948]  
        )) AS UnpivotedTable2;
    
    OUTPUT @result 
    TO "/Output/ReferenceGuide/DML/From/Pivot/UnPivoted1.tsv"
    ORDER BY Band, Musician ASC
    USING Outputters.Tsv(outputHeader:true, nullEscape:"#null#");
    

**UNPIVOT and NULL handling**  
Compare the output from the two queries below:

    
    
    @data = 
      SELECT * FROM (VALUES
                     ("Band1", "Musician1", "Instrument1", (int?)1,    (int?)null  ),
                     ("Band2", "Musician2", "Instrument2", (int?)null, (int?)null  ),
                     ("Band3", "Musician3", "Instrument3", (int?)null, (int?)3     )
                    ) AS T(Band, Musician, Instrument, [1940], [1941]);
    
    // using INCLUDE NULLS
    @result = 
      SELECT Band, Musician, YearOfBirth, Instrument, Children 
      FROM @data
           UNPIVOT INCLUDE NULLS (Children FOR YearOfBirth IN([1940], [1941])) AS UnpivotedTable;
    
    OUTPUT @result 
    TO "/ReferenceGuide/DML/FROM/PIVOT/unpivotIncludeNulls.txt"
    USING Outputters.Tsv(outputHeader:true);
    
    
    // using EXCLUDE NULLS
    @result = 
      SELECT Band, Musician, YearOfBirth, Instrument, Children 
      FROM @data
           UNPIVOT EXCLUDE NULLS (Children FOR YearOfBirth IN([1940], [1941])) AS UnpivotedTable;
    
    OUTPUT @result 
    TO "/ReferenceGuide/DML/FROM/PIVOT/unpivotExcludeNulls.txt"
    USING Outputters.Tsv(outputHeader:true);
    

##  See Also

  * [ FROM Clause (U-SQL) ](../from-clause)
  * [ MAP_AGG (U-SQL) ](../../../functions/aggregate/map-agg) (alternative method to pivot results) 

* * *

##  Additional resources

[ California Consumer Privacy Act (CCPA) Opt-Out Icon  Your Privacy Choices
](https://aka.ms/yourcaliforniaprivacychoices)

Theme

  * Light 
  * Dark 
  * High contrast 

  *   * [ Previous Versions ](/en-us/previous-versions/)
  * [ Blog ](https://techcommunity.microsoft.com/t5/microsoft-learn-blog/bg-p/MicrosoftLearnBlog)
  * [ Contribute ](/en-us/contribute/)
  * [ Privacy ](https://go.microsoft.com/fwlink/?LinkId=521839)
  * [ Terms of Use ](/en-us/legal/termsofuse)
  * [ Trademarks ](https://www.microsoft.com/legal/intellectualproperty/Trademarks/)
  * © Microsoft 2024 

##  Additional resources

###  In this article

[ California Consumer Privacy Act (CCPA) Opt-Out Icon  Your Privacy Choices
](https://aka.ms/yourcaliforniaprivacychoices)

Theme

  * Light 
  * Dark 
  * High contrast 

  *   * [ Previous Versions ](/en-us/previous-versions/)
  * [ Blog ](https://techcommunity.microsoft.com/t5/microsoft-learn-blog/bg-p/MicrosoftLearnBlog)
  * [ Contribute ](/en-us/contribute/)
  * [ Privacy ](https://go.microsoft.com/fwlink/?LinkId=521839)
  * [ Terms of Use ](/en-us/legal/termsofuse)
  * [ Trademarks ](https://www.microsoft.com/legal/intellectualproperty/Trademarks/)
  * © Microsoft 2024