SQLITE change colum name to value from cells

Question

I have a simple database with two tables:

dates (ID - PK, data - text):

ID | data      
1  | 2013-02-03    
2  | 2012-10-06  
3  | 2014-04-22

and rate (ID - PK, dataNR - FK from dates(ID), value (range 0-6), userNR (from other base - it's not important):

ID | value | dataNR | userNR
1  | 3     | 1      | 1
2  | 5     | 2      | 1
3  | 2     | 3      | 2

and when I use this command:

SELECT rate.ID, rate.value, dates.data, rate.userNR
FROM rate
INNER JOIN date ON rate.dataNR = dates.ID

I believe the result would be:

ID | value | dataNR       | userNR
1  | 3     | 2013-02-03   | 1
2  | 5     | 2012-10-06   | 1
3  | 2     | 2014-04-22   | 2

but I would like to get:

userNr | 2013-02-03 | 2012-10-06 | 2014-04-22
 1     | 3          | 5          |
 2     |            |            | 2

How can i get it? I would like to add that it will be connect with c# (component datagridView, well it maybe shows new ways to result?).

I have edited your question. Please verify that I correctly understood your problem. Also you list three different RDBMS (`MySQL`, `SQLite`, and `PostgreSQ`L). Which one do you actually use? — PM 77-1, Apr 06 '14 at 01:45
Then remove the two other tags. Also, how large are your tables actually? — PM 77-1, Apr 06 '14 at 01:48
actually - 5-10 rows, in future - max 500-1000 rows for rate, but for data: 20-30 different times, look at edit :-) I added some new precision dates. — cniak, Apr 06 '14 at 01:51
And you need a result with 500 columns? Can you do `GROUP BY` instead and then massage the data in your front end? — PM 77-1, Apr 06 '14 at 01:53
Are `data` values unique or do you need to total rows with the same `data` value? — PM 77-1, Apr 06 '14 at 01:55
no, i would get result with max 20-30 columns :) @edit: datatime - unique — cniak, Apr 06 '14 at 01:55
It doesn't seem that you thought it through. Create a more realistic data set and post it with the desired result. — PM 77-1, Apr 06 '14 at 02:01
Ok, i'm editing now first post and add more practical example (5 min). — cniak, Apr 06 '14 at 02:02
Have a look at [Pivot Table Hack in Sqlite3 and MySQL](http://softwaresalariman.blogspot.com/2008/05/pivot-table-hack-in-sqlite3-and-mysql.html). This is a very static approach. In your case it will be better to grab the data from a simple join into C# array and do the pivoting on front-end side. — PM 77-1, Apr 06 '14 at 02:34
possible duplicate of [Pivot in SQLite](http://stackoverflow.com/questions/1237068/pivot-in-sqlite) — CL., Apr 06 '14 at 08:57

SQLITE change colum name to value from cells

0 Answers0