sql-server - 如何从 TSQL UNPIVOT 中过滤值？

Question

我有一个面向列的表，用于指定产品的制造选项。每个选项可以是以下选项的子集(' '、'B'、'L'、'R'、'BX'、'LX'、'RX)。该表有 314 列。 (这是一个遗留版本，而不是我今天做的方式......)

我有一个客户要求计算某个选项在某个日期范围内的使用次数。获取过滤后的数据不是问题，但是将面向列的表转换为可数的面向行的表是对我的透视/反透视技能的挑战。

我可以用这个获取列名(从 http://www.simple-talk.com/community/blogs/andras/archive/2007/09/14/37265.aspx 引用)

declare @cols nvarchar(max)

select @cols = coalesce(@cols + ',' + quotename(column_name), quotename(column_name))
from information_schema.columns where table_name = 'mfgOptions'
    and data_type = 'char' and character_maximum_length

大多数情况下，选项都有一个 ' ' 值。我不想将这些包含在我的数据透视表中。
我想要的结果是一个表格，如:

MfgItemID | MfgOptionName | OptionSelection
-------------------------------------------
1000      | option1       | BX
1000      | option2       | B
2000      | option1       | LX
2000      | option3       | RX

使用上面的@cols 定义，我创建了以下 UNPIVOT

declare @query nvarchar(max)
set @query = N'SELECT MfgItemID, MfgOptionName, OptionSelection
from (SELECT MfgItemID, ' + @cols + '
FROM mfgOptions) a
UNPIVOT (OptionSelection for MfgOptionName IN (' + @cols + ') ) AS u
order by MfgItemID, MfgOptionName'

EXECUTE(@query)

这个查询正在执行并且似乎主要做我需要的。但是，由于表中有 500,000 多行，因此需要很长时间。如果我可以在取消旋转之前消除所有空白选项，我认为它会运行得更快。

有没有人对如何在取消透视之前过滤字段值有任何建议？

Answer 1

你不能放 WHERE内UNPIVOT子句，并且您无法在返回 a 的子查询中过滤掉因为该行上可能还有其他有用的值。我知道你能做的最好的事情就是说 WHERE u.OptionSelection <> ' '后 UNPIVOT像这样:

declare @query nvarchar(max)
set @query = N'SELECT MfgItemID, MfgOptionName, OptionSelection
from (SELECT MfgItemID, ' + @cols + '
FROM mfgOptions) a
UNPIVOT (OptionSelection for MfgOptionName IN (' + @cols + ') ) AS u
WHERE u.OptionSelection <> '' ''
order by MfgItemID, MfgOptionName'

EXECUTE(@query)