I work with a medical recording system that stores data in a design that resembles a spreadsheet — the date / time in the column headings, the measurements (for example, doctor’s name, Rh, blood type) in the first column of each row, and the value in the intersecting cell. Reports based on this design often require the display of 10 or more of these measures.
For reporting purposes, the data set should have one row for each patient, date / time of measurement, and a column for each measurement. In fact, you need to rotate the design 90 degrees.
At one point, I actually used the SQL Server PIVOT functionality to do just that. For a number of reasons, it has become apparent that this approach will not work. I decided that I would use the built-in view (IV) to massage the data in the desired format. A simplified query is like:
SELECT patient_id,
datetime,
m1.value AS physician_name,
m2.value AS blood_type,
m3.value AS rh
FROM patient_table
INNER JOIN ( complex query here
WHERE measure_id=1) m1...
INNER JOIN (complex query here
WHERE measure_id=2) m2...
LEFT OUTER JOIN (complex query here
WHERE measure_id=3) m3...
As you can see, in some cases these IVs are used to limit the result set (INNER JOIN), in other cases they do not limit the data set (LEFT OUTER JOIN). However, the "complex query" part is essentially the same for each of these measures, except for the difference in measure_id. Although this approach works, it leads to fairly large SQL statements, restricts reuse, and causes the query to fail.
, " " WHERE Inline Table-Value UDF. , . , , - . UDF ? ?
.