CODEWITHSUNDEEP
sqlsql-serverunionunpivot
lhan
lhan

Reputation: 4635

Rewriting SQL Server query with multiple UNIONs

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.

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.

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

Upvotes: 2

Views: 1786

Answers (1)

Taryn
Taryn

Reputation: 247680

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

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:

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:

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

Upvotes: 3

Related Questions