PIVOT in SQLIte_问答_开发者_运维开发者技术经验分享

I'd like to get a table which shows parameters and values they receive for all of their parametervalues in one query.

This is my table structure:

tbl_parameter

parameterid parametername

tbl_parametervalues

parameterid parametervalue

Actual Structure in tbl_parameter

  --------------------------------
  ----------------------------------
  | parameterid |  parametername    |
  |----------------------------
  |   TYPE      |   Type            |
  |   TEMP      |   Temp(Deg.C)     |
  |   TIME      |   Time            |
  |   DATE      |   Date        开发者_StackOverflow中文版    |
  |  TECHNICIAN |   Technician      |
  |  TESTLENGTH |   Test Length     |
  |  TESTRESULT |   Test Result     |
  -----------------------------------

Actual Structure in tbl_parametervalues

  ------------------------------------
  | parameterid |  parametervalue    |
  |----------------------------
  |   TYPE      |   DW1            |
  |   TEMP      |   21             |
  |   TIME      |   10:45 PM       |
  |   DATE      |   14/09/2011     |
  |  TECHNICIAN |   Test1          |
  |  TESTLENGTH |   12             |
  |  TESTRESULT |   Pass           |
  |   TYPE      |   DW2            |
  |   TEMP      |   22             |
  |   TIME      |   11:45 PM       |
  |   DATE      |   15/09/2011     |
  |  TECHNICIAN |   Test2          |
  |  TESTLENGTH |   12             |
  |  TESTRESULT |   Pass           
  -----------------------------------

I want the result set to look like this:

  -----------------------------------------------------------------------------
  | SL NO |  Type    | Temp |  Time  | Date     | Technician | Test   |Test   |
  |                                                          | Length |Result |
  ---------------------------------------------------------------------------
  | 1     | DW1      |  21  |10:45 PM|14/09/2011| Test1      | 12     | Pass  |
  | 2     | DW2      |  22  |11.45   | 15/09/2011| Test2     | 12     | Pass  |
  |------------------------------------------------------------------------------

How can I accomplish this in SQLite?

I could not find a definition of how to detect sets of parameter values, i.e. which "TEMP" belongs to which "TYPE". So I assume that the sets of parameter values are always entered into the database consecutively and in the order given in the question. The comment by OP seems to allow this assumption. It is not very complicated (though a little) to immplement some robustness against shuffled orders (by associating via a detour by the parameterids), but I hope it is not necessary.

I also could not find information of what "SL NO" means and where the value is coming from. So I fake it by dividing the rowid of the TESTRESULT value by 7 (the number of different parameter names, which I consider to be the size of a parameter set). It should not be hard to dig the correct values up from your database. It is not required that the rowids are on multiples of 7, as long as the parameters are entered consecutively. Just the "SL NO" might skip a few numbers, if the rowids of e.g. "TYPE" are multiples of e.g. 8, otherwise the query tolerates gaps between parameter sets.

You can find the non-query part of my MCVE at the end of this answer.

Query:

select
    'SL NO',
    TYPE.parametername, 
    TEMP.parametername,
    TIME.parametername,
    DATE.parametername,
    TECHNICIAN.parametername,
    TESTLENGTH.parametername,
    TESTRESULT.parametername
from
    parameter TYPE,
    parameter TEMP,
    parameter TIME,
    parameter DATE,
    parameter TECHNICIAN,
    parameter TESTLENGTH,
    parameter TESTRESULT
where TYPE.parameterid='TYPE'
 and TEMP.rowid=TYPE.rowid+1
 and TIME.rowid=TYPE.rowid+2
 and DATE.rowid=TYPE.rowid+3
 and TECHNICIAN.rowid=TYPE.rowid+4
 and TESTLENGTH.rowid=TYPE.rowid+5
 and TESTRESULT.rowid=TYPE.rowid+6   

UNION ALL

select
    TESTRESULT.rowid/7,
    TYPE.parametervalue, 
    TEMP.parametervalue,
    TIME.parametervalue,
    DATE.parametervalue,
    TECHNICIAN.parametervalue,
    TESTLENGTH.parametervalue,
    TESTRESULT.parametervalue
from
    parametervalues TYPE,
    parametervalues TEMP,
    parametervalues TIME,
    parametervalues DATE,
    parametervalues TECHNICIAN,
    parametervalues TESTLENGTH,
    parametervalues TESTRESULT
where TYPE.parameterid='TYPE'
 and TEMP.rowid=TYPE.rowid+1
 and TIME.rowid=TYPE.rowid+2
 and DATE.rowid=TYPE.rowid+3
 and TECHNICIAN.rowid=TYPE.rowid+4
 and TESTLENGTH.rowid=TYPE.rowid+5
 and TESTRESULT.rowid=TYPE.rowid+6
 ;

make one table on the fly for each pivot column
select from each of those tables the entry for one column
associate entries for the same line via assumptions (as stated above) on rowids

Output:

SL NO       Type           Temp(Deg.C)    Time           Date           Technician     Test Length    Test Result
1           DW1            21             10:45 PM       14/09/2011     Test1          12             Pass
2           DW2            22             11.45           15/09/2011    Test2          12             Pass

MCVE (.dump):

BEGIN TRANSACTION;
CREATE TABLE parametervalues(parameterid varchar(30), parametervalue varchar(30) );
INSERT INTO parametervalues(parameterid,parametervalue) VALUES('TYPE','DW1');
INSERT INTO parametervalues(parameterid,parametervalue) VALUES('TEMP','21');
INSERT INTO parametervalues(parameterid,parametervalue) VALUES('TIME','10:45 PM');
INSERT INTO parametervalues(parameterid,parametervalue) VALUES('DATE','14/09/2011');
INSERT INTO parametervalues(parameterid,parametervalue) VALUES('TECHNICIAN','Test1');
INSERT INTO parametervalues(parameterid,parametervalue) VALUES('TESTLENGTH','12');
INSERT INTO parametervalues(parameterid,parametervalue) VALUES('TESTRESULT','Pass');
INSERT INTO parametervalues(parameterid,parametervalue) VALUES('TYPE','DW2');
INSERT INTO parametervalues(parameterid,parametervalue) VALUES('TEMP','22');
INSERT INTO parametervalues(parameterid,parametervalue) VALUES('TIME','11.45');
INSERT INTO parametervalues(parameterid,parametervalue) VALUES('DATE',' 15/09/2011');
INSERT INTO parametervalues(parameterid,parametervalue) VALUES('TECHNICIAN','Test2');
INSERT INTO parametervalues(parameterid,parametervalue) VALUES('TESTLENGTH','12');
INSERT INTO parametervalues(parameterid,parametervalue) VALUES('TESTRESULT','Pass');
CREATE TABLE parameter (parameterid varchar(30), parametername varchar(30));
INSERT INTO parameter(parameterid,parametername) VALUES('TYPE','Type');
INSERT INTO parameter(parameterid,parametername) VALUES('TEMP','Temp(Deg.C)');
INSERT INTO parameter(parameterid,parametername) VALUES('TIME','Time');
INSERT INTO parameter(parameterid,parametername) VALUES('TATE','Date');
INSERT INTO parameter(parameterid,parametername) VALUES('TECHNICIAN','Technician');
INSERT INTO parameter(parameterid,parametername) VALUES('TESTLENGTH','Test Length');
INSERT INTO parameter(parameterid,parametername) VALUES('TESTRESULT','Test Result');
COMMIT;

Note1:
I skipped the line breaks for the headers of the "Test *" parameter names. That seems irrelevant.

Note2:
My MCVE contains values to match the desired output, not always identical to the sample input. If the values are taken from sample input, the output looks different - and more convincing. It is not important which side of the inconsistence is correct and which is the typo. I chose to implement the version which requires more flexibility.