CODEWITHSUNDEEP
sqlt-sqlsql-server-2012
Grambot
Grambot

Reputation: 4524

Querying one of many columns based on value

First off, the schema and data layout are out of my control. If I had more control over the storage I'd happily make some adjustments but this is what I'm forced to work with.

Within our database we store a number of "Factors" for each product transaction. These factors change based on the product type but are often reused but aren't necessarily in the same order from product to product.

What I'm hoping is possible (but suspecting it isn't with a large amount of dynamic query building) is a way to query a transaction and only retrieve the factors that match a given input. An example that might help:

Example table

ID | ProductID | Factor01 | Value01 | Factor02 | Value02 | .... | Factor15 | Value15  
------------------------------------------------------------------------------------
 1 |        15 |    PROT  |   10.1  |       MO |     3.0 | .... |          |
 2 |        16 |      MO  |    2.8  |       DK |    11.6 | .... |          |
 3 |        17 |      MO  |    5.6  |     PROT |    12.6 | .... |     GRN  |     0.8

So, if I need to query the table to get the value for 'MO' in each case we currently have to query the entire 15 values then loop through the data within the application to locate the correct value and display it.

Is there any way to let SQL handle this lifting? The program that we're developing in isn't overly strong at handling data so its often much easier to just give it a view to return data that it can query.

Can I provide any more details to help? Part of me assumes this isn't possible without complex functions the neutered development environment won't be able to handle.

EDIT: SQL Server 2012, and as @AHiggins requested there will never be more than 15 factors, but some products may only use 5, leaving 6-15 NULL or blank.

Upvotes: 0

Views: 44

Answers (1)

bwperrin
bwperrin

Reputation: 692

if you're using SQL2005 or later, use cross apply and union all:

select t.ID, t.ProductID, fv.Factor, fv.Value, fv.pos
from table t cross apply (
    select pos = 1, factor = factor01, value = value01 where factor01 <> ''
    UNION ALL select 2, factor02, value02 where factor02 <> ''
    UNION ALL select 3, factor03, value03 where factor03 <> ''
    ...
    UNION ALL select 15, factor15, value15 where factor15 <> ''
    ) fv

It'll perform very well. UNPIVOT is ok too, but IMO this is more readily understandable.

Upvotes: 2

Related Questions