0

Assume I have 2 tables FOO and BAR as below, is it possible to use a sort of reflection on FOO, if you know the column name as a string based on a join of the BAR table?

SELECT DB, FOO.Name, FOO.Type, BAR.Field, I.DATA_TYPE AS FType, FOO.**<BAR.FIELD>** AS Value
FROM INFORMATION_SCHEMA.COLUMNS AS I  
inner JOIN BAR ON I.COLUMN_NAME = BAR.Field
inner JOIN FOO ON FOO.TYPE = BAR.TYPE
WHERE DB = 4 AND FLAG = 1

i.e. for each selected row, FOO. need to change to reflect the value of the matching column in FOO, i.e. if one row has BAR {4, AC1, LO} and FOO { 4, AC1, LO, COL1} I want the value of 1 to be picked.

I know that I can probably do this is 2 rounds and merge the the data, however I wondered if anybody would know of a way to do this more efficiently in 1 go, saving code path.

I should add, I generally have around 60 columns in either table, and they are pretty random, i.e. I cannot assume that either col1, 2 or 3 exist, I can only go by what is in the equivalent BAR table.

FOO:

+--------+--------+---------+---------+--------+-------+
|   DB   | Name   | Type    | Col1    |  Col2  | Col3  |
+--------+--------+---------+---------+--------+-------+
|   4    | AC1    | LO      | 1       |  10    | 2     |
|   4    | AC1    | HI      | 2       |  20    | 4     |
|   1    | DC2    | HI-HI   | 11      |  5     | 2     |
|   1    | DC2    | HI      | 22      |  10    | 4     |
|   1    | DC2    | LO      | 33      |  15    | 6     |
+--------+--------+---------+---------+--------+-------+

BAR:

+--------+--------+---------+---------+--------+
|   DB   | Name   | Type    | Field   |  Flag  |
+--------+--------+---------+---------+--------+
|   4    | AC1    | LO      | Col1    |  1     |
|   4    | AC1    | HI      | Col1    |  1     |
|   1    | DC2    | HI-HI   | Col1    |  1     |
|   1    | DC2    | HI      | Col1    |  1     |
|   1    | DC2    | LO      | Col1    |  1     |
|   4    | AC1    | LO      | Col2    |  0     |
|   4    | AC1    | HI      | Col2    |  0     |
|   1    | DC2    | LO      | Col2    |  0     |
|   1    | DC2    | HI-HI   | Col2    |  0     |
|   1    | DC2    | HI      | Col2    |  0     |
|   4    | AC1    | LO      | Col3    |  0     |
|   4    | AC1    | HI      | Col3    |  0     |
|   1    | DC2    | LO      | Col3    |  0     |
|   1    | DC2    | HI-HI   | Col3    |  0     |
|   1    | DC2    | HI      | Col3    |  0     |
+--------+--------+---------+---------+--------+

RESULT:

+--------+--------+---------+---------+--------+--------+
|   DB   | Name   | Type    | Field   |  FTYPE |  VALUE |
+--------+--------+---------+---------+--------+--------+
|   4    | AC1    | LO      | Col1    |  float |  1     |
|   4    | AC1    | HI      | Col1    |  float |  2     |
|   4    | AC1    | LO      | Col2    |  float |  10    |
|   4    | AC1    | HI      | Col2    |  float |  20    |
|   4    | AC1    | LO      | Col3    |  float |  2     |
|   4    | AC1    | HI      | Col3    |  float |  4     |
+--------+--------+---------+---------+--------+--------+
aggaton
  • 3,066
  • 2
  • 25
  • 36
  • "is it possible to use a sort of reflection on FOO, if you know the column name as a string based on a join of the BAR table?" `Yes, but it requires the use of `[dynamic SQL](http://stackoverflow.com/questions/4165020/what-is-dynamic-sql) – xQbert Apr 26 '13 at 19:59
  • So you are suggesting building the SQL on the DAL side, inserting a select block per column name and then stitch all the selects together with UNION ALL's? That is sort of what I was trying to avoid, since it would still require 2 calls, for me to get a list of all the possible column names. – aggaton Apr 26 '13 at 20:08
  • there is no way besides dynamic sql for that thing on server. You can create sp with that dynamic stuff and use it from client. – shibormot Apr 27 '13 at 02:20
  • No I'm not suggesting union all's or anything of the sort. A stored procedure could be written which dynamically creates SQL (click on dynamic SQL in 1st comment for an explanation) that SQL is then executed and returns the result set desired. The DOWNFall to this approach is that dynamic SQL doesn't get benefits of pre-compile. However, it would be 1 trip which does reduce code path. The "Sort of reflection" your trying to do is called Dynamic SQL. Now that I look closer at this though, I really think all you need is knowledge of system tables and a pivot statement. – xQbert Apr 29 '13 at 17:19
  • information_schema.columns to get Ftype and column name based on table name, use the tables themselves to get values for each field. It might be a combination of both Dynamic SQL and system tables and pivot to do it all... – xQbert Apr 29 '13 at 17:22

0 Answers0