Van Van - 4 months ago 9
SQL Question

MSSQL/TSQL separating fields into rows based on value

I have two tables with data like:

table: test_results

ID |test_id |test_type |result_1 |amps |volts |power |
----+-----------+-----------+-----------+-----------+-----------+-----------+
1 |101 |static |10.1 |5.9 |15 |59.1 |
2 |101 |dynamic |300.5 |9.1 |10 |40.1 |
3 |101 |prime |48.9 |8.2 |14 |49.2 |
4 |101 |dual |235.2 |2.9 |11 |25.8 |
5 |101 |static |11.9 |4.3 |9 |43.3 |
6 |101 |prime |49.9 |5.8 |15 |51.6 |


and

table: test_records

ID |model |test_date |operator |
----+-----------+-----------+-----------+
101 |m-300 |some_date |john doe |
102 |m-243 |some_date |john doe |
103 |m-007 |some_date |john doe |
104 |m-523 |some_date |john doe |
105 |m-842 |some_date |john doe |
106 |m-252 |some_date |john doe |


and I'm making a report that looks like this:

|static |dynamic |
test_id |model |test_date |operator |result_1 |amps |volts |power |result_1 |amps |volts |power |
-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
101 |m-300 |some_date |john doe |10.1 |5.9 |15 |59.1 |300.5 |9.1 |10 |40.1 |


with left outer joins like so:

SELECT
A.ID AS test_id, model, test_date, operator,
B.result_1, B.amps, B.volts, B.power,
C.result_1, C.amps, C.volts, C.power
FROM
test_records A
LEFT JOIN
test_results B
ON
A.ID = B.test_id
AND
B.test_type = 'static'
LEFT JOIN
test_results C
ON
A.ID = C.test_id
AND
C.test_type = 'dynamic'


But I have run into a problem. The "static" and "prime" tests are run twice.
I don't know how to differentiate between them to create their own 4 fields.
An abstracted(simplified) view of the desired report would look like:

|static |dynamic |prime |dual |static2 |prime2 |
|4 fields |4 fields |4 fields |4 fields |4 fields |4 fields |


Is this even possible?
Notes:


  1. I'm labeling the groups of 4 fields with html so don't worry about the labels

  2. Not every test will run "static" and "prime" twice. So this is a case of If ("static" and "prime") are found twice, do this SQL.

  3. I think we're going to get our engineers to append a 2 to the second tests, eliminating the problem, so this question is more out of curiosity to know what method could solve a problem like this.


Answer

If you have another field (here I use ID) that you know is always going to be ordered in respect to the field you can use a windowing function to give them sequential values and then join to that. Like this:

WITH test_records_numbered AS
(  
   SELECT test_id, test_type, result_1, amps, volts, power, 
          ROW_NUMBER() OVER (PARTITION BY test_id, test_type ORDER BY ID) as type_num
   FROM test_records
)
SELECT
    A.ID AS test_id, model, test_date, operator,
    B.result_1, B.amps, B.volts, B.power,
    C.result_1, C.amps, C.volts, C.power
FROM test_records A
LEFT JOIN test_results_numbered B
      ON A.ID = B.test_id AND B.test_type = 'static' and B.type_num = 1
LEFT JOIN test_results_numbered C 
      ON A.ID = C.test_id AND C.test_type = 'dynamic' and C.type_num = 2

I use a CTE to make it clearer but you could use a sub-queries, you would (of course) have to have the same sub-query twice in the SQL, most servers would have no issue optimizing without the CTE I expect.

I feel this solution is a bit of a "hack." You really want your original data to have all the information it needs. So I think it is good you are having your app developers modify their code (FWIW).

If this had to go into production I think I would break out the numbering as a view to hi-light the codification of questionable business rules (and to make it easy to change)

Comments