Advice -- How to find out the common denominator

• boeledi

  Ten Centuries

  Points: 1151

  More actions

  February 26, 2014 at 6:01 am

  #282947

  Hi,

  I would like to have your advice on the following problem.

  Say I have the following matrix that defines parameters (1 parameter type = 1 column) which are defined for each category (1 category = 1 row)

  opt_1opt_2opt_3opt_4opt_5opt_6opt_7sel_1sel_2sel_3sel_4sel_5

  cat_1 011111001001

  cat_2 111101010110

  cat_3 011101111110

  cat_4 110010010111

  cat_5 100111110101

  cat_6 001110011010

  cat_7 110000111000

  cat_8 111111000100

  cat_9 000110000110

  I need to find out a way of retrieving the list of common "columns" (=1) for a series of categories.

  Example: If I consider cat_2, cat_4 and cat_5, I need to retrieve: opt_1, sel_1, sel_3

  How would you do this, knowing that the list of "columns" will very with the time and that I need the query to be very quick.

  Many thanks in advance,

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  February 26, 2014 at 6:43 am

  #1692257

  It sounds as if this is in the design phase - if this is the case, then consider pivoting the table you display here as your starting point, so columns are cat_1 to cat_n. Retrieving rows and pivoting the result set would be trivial.

  What's this for?

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• EricEyster

  SSCrazy

  Points: 2974

  More actions

  February 26, 2014 at 7:56 am

  #1692291

  ChrisM@Work (2/26/2014)

  ---

  It sounds as if this is in the design phase - if this is the case, then consider pivoting the table you display here as your starting point, so columns are cat_1 to cat_n. Retrieving rows and pivoting the result set would be trivial.

  What's this for?

  Agreed. It sounds like you are storing 1 to many sub-values for a category, which from experience always changes.