Pierre Lebon Pierre Lebon -4 years ago 104
SQL Question

Dynamically Select Column Based on a Bytefield

To create a set of Items, we compare all items to find those which have 'Something' in common.

This little 'Something' is defined by a table that stores ColumnName and a ByteValue. In order to know which Column we need to make a comparison.

As a picture often says more than words, here is a basic sketch.

Table Parameter that stores the ByteField signification:

In Green and example of Column combination use as comparator, giving us

71
as Comparator.

Table Parameter that store the ByteField Signification

Table data :
enter image description here

How to build a query returning a list of Data Id, taking as input a Data Id as base for comparison and a predefined combination of Column? Here
71
.

For this example lets say we look for Set item based on DataID=1 and Byte=71. The expected result is :
1, 4, 5
.

Here is a sample of the database.

And because i was "bitten once", I'm not asking you to do my job. I am handling it with some code but I would love to see how to do that in T-SQL. That's why I provided a sample table.

Answer Source

There is a mismatch between the columns of table "Data" and the values defined in column "Parameter".


With static SQL you have to know the names of the columns in advance.

declare @compactor  int = 71
        ,@DataID    int = 1

select        *
from
      ( select  t.*
                ,min (case "ID" when @DataID then 'Y' end) over (partition by rnk) as is_requested_row
        from   ( select t.*
                        , rank () over
                            (
                                order by  case when @compactor / power(2,0) % 2 = 1 then "Color" end
                                         ,case when @compactor / power(2,1) % 2 = 1 then "Type" end
                                         ,case when @compactor / power(2,2) % 2 = 1 then "Type2" end
                                         ,case when @compactor / power(2,3) % 2 = 1 then "Name" end
                                         ,case when @compactor / power(2,4) % 2 = 1 then "Sci_name"   end
                                         -- ,case when @compactor / power(2,5) % 2 = 1 then "Alias"     end
                                         ,case when @compactor / power(2,6) % 2 = 1 then "Version"  end
                                         -- ,case when @compactor / power(2,7) % 2 = 1 then "Firmware"  end
                                         -- ,case when @compactor / power(2,8) % 2 = 1 then "Brand"     end
                                         -- ,case when @compactor / power(2,9) % 2 = 1 then "Avaidable" end
                            )   as rnk
                  from   "Data" as t
                ) t
        ) t
where is_requested_row = 'Y'
order by    "ID" ;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download