SQL pivot并填写空值。

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

在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

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”列具有每行唯一值，然后为“size”中出现的每个值设置列。如果某一项的大小不存在，则插入NULL值。

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

2 个解决方案

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

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

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

#2

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

NULL是不满足条件的情况下的结果。

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