Ok, I know that the topic doesn’t make much sense. So I’ll let the pictures talk here. I’ve seen terrible ideas for showing the column header for sql output (even creating TABLES for each fields!!) so I am posting this small snippet. Here we go
Let’s assume that we have a table, named as “TEST”. And we have some fields: test_data, test_data_other, test_data_name and test_date Create statement:
CREATE TABLE TEST (test_data VARCHAR(30), test_data_other VARCHAR(30),
test_date SMALLDATE, test_data_name VARCHAR(6));
Some dummy data entry:Note: We don’t have a primary key field here. In normal conditions you’ll probably have “test_data_name” field as your primary key, but just to show the problem and the solution, I’m going to skip that part.
INSERT INTO TEST VALUES('SAMPLE DATA', 'ABC VALUE', '12-05-2008', 'ABC COMPANY');
INSERT INTO TEST VALUES('SAMPLE DATA', 'DEF VALUE', '12-05-2008', 'DEF COMPANY');
INSERT INTO TEST VALUES('SAMPLE DATA', 'QWE VALUE', '12-05-2008', 'QWE COMPANY');
INSERT INTO TEST VALUES('SAMPLE DATA', 'YZX VALUE', '12-05-2008', 'YZX COMPANY');
INSERT INTO TEST VALUES('SAMPLE DATA', '123 VALUE', '13-05-2008', 'ABC COMPANY');
INSERT INTO TEST VALUES('SAMPLE DATA', '345 VALUE', '12-05-2008', 'DEF COMPANY');
INSERT INTO TEST VALUES('SAMPLE DATA', '678 VALUE', '12-05-2008', 'QWE COMPANY');
INSERT INTO TEST VALUES('SAMPLE DATA', '901 VALUE', '12-05-2008', 'YZX COMPANY');
INSERT INTO TEST VALUES('SAMPLE DATA', '321 VALUE', '13-05-2008', 'ABC COMPANY');
INSERT INTO TEST VALUES('SAMPLE DATA', '432 VALUE', '12-05-2008', 'DEF COMPANY');
INSERT INTO TEST VALUES('SAMPLE DATA', '653 VALUE', '12-05-2008', 'QWE COMPANY');
INSERT INTO TEST VALUES('SAMPLE DATA', '234 VALUE', '12-05-2008', 'YZX COMPANY');
How the table looks like with a SELECT * FROM TEST; query:
How do we want the output to look like:
So, how do we get this output? We can use Fetch() in MsSql or some other servers, but with SQLite ? We use Case Then End code to get this output:
SELECT TEST_DATA_NAME, MIN(CASE WHEN TEST_DATE='12-05-2008' THEN TEST_DATA_OTHER END) AS '12-05-2008', MIN(CASE WHEN TEST_DATE='13-05-2008' THEN TEST_DATA_OTHER END) AS '13-05-2008', MIN(CASE WHEN TEST_DATE='14-05-2008' THEN TEST_DATA_OTHER END) AS '14-05-2008' FROM TEST GROUP BY TEST_DATA_NAME;
Note that SQLite only recognizes the date fields if you use “YYYY-MM-DD HH:MM:SS” format. We are using DD-MM-YYYY only, so we cant use date functions here.
So here, we check if the date column is what we want, and if it is, we ask SQLite to show our desired field as column. I’m using this technique in a stock market application, to show the daily values of stock items (there are 350 items and their value changes every day, so I need to see them as a column, not as a row) Another note. We use MIN(…). Why do we use an aggregate function(we are not even looking for MIN values anyways!!) Well, try using this query without an aggregate function. The “GROUP BY” clause won’t work. So we have to use an aggregate function, which, in our case is, MIN.
Cheers and happy coding
Bora Bilgin
Operating Systems and Software | Bora Bilgin | 
December 26, 2009 1:54 am | 
Comments (1) 
I’ve made a networked version of this (which simply runs in background and sends the audio data to multiple computers on network) but I think it won’t be good to distribute that application. So, enjoy 
)
Subscribe