SQL pivot 并填充 NULL 值

Question

在 SQL (SQLite) 中，我有一个“权重”表，其中包含每个项目大小的权重，例如

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

我如何旋转表格，使“项目”列的每个唯一值各占一行，然后为“大小”中出现的每个值创建列。如果元素的给定尺寸不存在重量，则会插入 NULL 值，例如

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

Answer 1

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

注意:我已经很多年没有使用 SQLite 了。不确定这种动态方法是否有效