SQL pivot and fill in NULL values

Question

In SQL (SQLite) I have a table 'weights' containing weights per size of an item e.g.

item size    weight
---- ----    ------
 a   small     1.2
 a   medium    2.2
 b   medium    1.6
 c   small     1.0
 c   medium    1.5
 c   large     2.0

How can I pivot the table so I have the 'item' column with one row per unique value, then have columns for each value that appears in 'size'. Where a weight isn't present for a given size of an item, a NULL value is inserted instead e.g.

item small medium large
---- ----- ------ -----
 a    1.2   2.2   NULL
 b    NULL  1.6   NULL
 c    1.0   1.5   2.0

Show source
| database   | sqlite   | sql   | null   | pivot   2017-01-04 20:01 2 Answers

Answers to SQL pivot and fill in NULL values ( 2 )

  1. 2017-01-04 20:01
    Select item
          ,max(case when size='small'  then weight else null end) as small
          ,max(case when size='medium' then weight else null end) as medium
          ,max(case when size='large'  then weight else null end) as large
     From weights
     Group By item
    

    EDIT - Dynamic version

    Declare @SQL varchar(max) = Stuff((Select Distinct ',' + QuoteName([size]) From YourTable Order by 1 For XML Path('')),1,1,'') 
    Select  @SQL = '
    Select [Item],' + @SQL + '
    From YourTable
     Pivot (max(weight) For [size] in (' + @SQL + ') ) p'
    Exec(@SQL);
    

    Note: I haven't used SQLite in years. Not sure if this dynamic approach will work

  2. 2017-01-04 20:01

    NULL is the result of a CASE when no condition is met.

    Select      item
               ,max(case when size='small'  then weight end) as small
               ,max(case when size='medium' then weight end) as medium
               ,max(case when size='large'  then weight end) as large
    From        weights
    Group By    item
    

    or

    Select      item
               ,max(case size when 'small'  then weight end) as small
               ,max(case size when 'medium' then weight end) as medium
               ,max(case size when 'large'  then weight end) as large
    From        weights
    Group By    item
    

Leave a reply to - SQL pivot and fill in NULL values

◀ Go back