I have a sort of brute for solution for the following problem, but I'm sure someone here will have an elegant solution.
I have the following horizontally oriented table.
| Person | Trait1 | Trait2 |
| Patrick | .3 | .4 |
| James | .7 | .3 |
| Kristin | .9 | .2 |
I would ideally like to separate the above into two vertical tables based on the traits. There would be two vertical tables, Bad and Terrible.
If .4 <= Trait1 < .6 -> Bad, Trait1 >= .7 -> Terrible
If .3 <= Trait2 < .5 -> Bad, Trait2 >= .9 -> Terrible
Using these conditions, we would then get the following tables
BAD
|Person | Trait | Value |
| Patrick | Trait2 | .4 |
| James | Trait2 | .3 |
Terrible
|Person | Trait | Value |
|James | Trait1 | .7 |
|Kristin | Trait1 | .9 |
Since you are using SQL Server 2005, you can use the UNPIVOT function to get the result.
The UNPIVOT takes the columns and converts it into rows which will be easier to apply your filter.
BAD Data:
select person, trait, value
into bad
from yourtable
unpivot
(
value
for trait in (trait1, trait2)
) u
where
(
trait ='trait1'
and value >= .4
and value < .6
)
or
(
trait ='trait2'
and value >= .3
and value < .5
);
Terrible Data:
select person, trait, value
into terrible
from yourtable
unpivot
(
value
for trait in (trait1, trait2)
) u
where
(
trait ='trait1'
and value >= .7
)
or
(
trait ='trait2'
and value >= .9
);
See SQL Fiddle with Demo.
Note, this can also be done using a UNION ALL query to UNPIVOT the columns into rows:
select person, trait, value
from
(
select person, 'trait1' trait, trait1 value
from yourtable
union all
select person, 'trait2' trait, trait2 value
from yourtable
) d
-- where apply the filter here.
The UNPIVOT and UNION ALL converts the data into the following format vertical format:
| PERSON | TRAIT | VALUE |
----------------------------
| Patrick | trait1 | 0.3 |
| James | trait1 | 0.7 |
| Kristin | trait1 | 0.9 |