SQL Server: How to convert a result of a query into two columns: Field Name and Value

Question

HI, now I have a simple question. This is a simple scenary:

I have a Table "Table1" with three fields (a1, a2, a3). The result of query is a simple row but I need to create this kind of query:

SELECT 'a1' as [Field], 
        a1 as [Value]
  FROM Table1
UNION
SELECT 'a2' as [Field], 
       a2 as [Value]
  FROM Table1
UNION
SELECT 'a3' as [Field], 
       a3 as [Value]
 FROM Table1

The expected result is a table with to fields ("Field" and "Value")

The Problem

...that I have is if I am joining three tables, I need at least 14 fields from this JOIN and I have the Where clause, so I have to repeat each FROM and WHERE clause on each UNION. At the end it is the mother of all queries. Is it possible to create a more simple query for this task?

I need the same result from my initial example for this more complex scenery.

thanks in advance.

Answer 1

You could use a common table expression for the join part so you're not repeating that:

WITH complex AS (
    Put big query here
)
SELECT 'a1' as [Field], a1 as [Value] FROM complex
UNION ALL 
SELECT 'a2' as [Field], a2 as [Value] FROM complex
UNION ALL 
SELECT 'a3' as [Field], a3 as [Value] FROM complex

Also you may want to have a look at UNPIVOT for the actual unpivoting instead of the UNION technique.

Answer 2

Is it possible to create a more simple query for this task?

Yes, but you should first look at making a change to your database schema. If that is not possible then you should stop reading now and explain to your boss (or whoever is obstructing you) that not being able to modify the schema could lead to an unmaintainable mess.

Assuming that you're still reading you should note that your database schema contains redundancy - you have similar columns that are repeating within the same table. This could be a sign the your database is not correctly normalized. These columns should most likely be moved out into a new table and instead of having separate columns 'a1', 'a2', 'a3' you can have the columns 'Value' and 'Field' where the former contains one of the values 'a1', 'a2', 'a3' and the latter contains the actual value . Now you can get all your data by doing a much simpler query without any repetition:

SELECT Field, Value
FROM ...
WHERE ...

If you need different where conditions for each type of field you can do that too:

WHERE (...common conditions...)
AND (
   (Field = 'a1' AND ...) OR
   (Field = 'a2' AND ...) OR
   ...
)