使用多个UNION重写SQL Server查询

Is there a better (more efficient) way to write this query? It seems like there should be a way to write it with only one UNION.

是否有更好(更有效)的方式来编写此查询?似乎应该有一种方法只用一个UNION来编写它。

Query:

SELECT 'Value1' as PropertyKey, PropertyValue=(SELECT Value1 FROM MyTable WITH (NOLOCK))
UNION
SELECT 'Value2' as PropertyKey, PropertyValue=(SELECT Value2 FROM MyTable WITH (NOLOCK))
UNION
SELECT 'Value3' as PropertyKey, PropertyValue=(SELECT Value3 FROM MyTable WITH (NOLOCK))
UNION
...
SELECT 'Value100' as PropertyKey, PropertyValue=(SELECT Value100 FROM MyTable WITH (NOLOCK))

Ultimately, I need my result set to have 2 columns (PropertyKey, and PropertyValue). The values in the PropertyKey column will be the names of the columns in my table, and the values in the PropertyValue column will be the corresponding values.

最终,我需要我的结果集有2列(PropertyKey和PropertyValue)。 PropertyKey列中的值将是我表中列的名称,PropertyValue列中的值将是相应的值。

Is it possible to write this with only one UNION if I'm always selecting from the same table?

如果我总是从同一个表中选择,是否可以只用一个UNION写这个?

1 个解决方案

#1

If you have access to the UNPIVOT function you can use it the following way:

如果您可以访问UNPIVOT功能,可以通过以下方式使用它:

select PropertyKey, PropertyValue
from yourtable
unpivot
(
  PropertyValue
  for PropertyKey in (Value1, Value2, Value3) -- list other columns here
) unpiv

The key piece to keep in mind with an UNPIVOT is the datatypes must be the same, so you might have to convert the datatypes:

要记住UNPIVOT的关键是数据类型必须相同,因此您可能必须转换数据类型:

select PropertyKey, PropertyValue
from
(
    select CAST(value1 as varchar(10)), 
         CAST(value1 as varchar(10)), ...
    from yourtable
) src
unpivot
(
  PropertyValue
  for PropertyKey in (Value1, Value2, Value3) -- list other columns here
) unpiv

You can even perform this dynamically, which will get the list of columns to transform at run-time:

您甚至可以动态执行此操作,这将获得要在运行时转换的列的列表:

DECLARE @colsUnpivot AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)    

select @colsUnpivot = stuff((select ','+quotename(C.name)
         from sys.columns as C
         where C.object_id = object_id('yourtable')
         for xml path('')), 1, 1, '')

set @query 
  = 'select PropertyKey, PropertyValue
     from yourtable
     unpivot
     (
        PropertyValue
        for PropertyKey in ('+ @colsunpivot +')
     ) u'

exec(@query)

If you are stuck, using your current version then I would alter it slightly and use a UNION ALL instead of the UNION:

如果您遇到困难,请使用当前版本,然后我会稍微更改它并使用UNION ALL而不是UNION:

SELECT 'Value1' as PropertyKey, Value1 PropertyValue
FROM MyTable
UNION ALL
SELECT 'Value2' as PropertyKey, Value2 PropertyValue
FROM MyTable

#1