0

New to sqlite3 database. Trying to retrieve the data from the table and pivot the data from Column and create a table view based upon that. I looked around and some suggested to use CASE and some suggested to use joins but i am not able to figure out how to write the sql statement to do the pivot on col2 in the table below. Col2 can have n number of rows but need to take only the unique values from col2 and use those unique values to pivot the table. Not sure if this is possible using sqlite3 or should I be doing this outside sqlite3 and use pandas?

Input data is as follows:

col1 col2 col3 col4 col5 col6 col7
1 test1 t1 Dummy1 result1 10 100.2
2 test1 t1 Dummy2 result2 20 101.2
3 test1 t1 Dummy3 result3 30 102.3
4 test1 t1 Dummy4 result4 40 101.4
5 test2 t1 Dummy1 result1 10 101
6 test2 t1 Dummy2 result2 20 103
7 test2 t1 Dummy3 result3 30 104
8 test2 t1 Dummy4 result4 40 105
9 test3 t1 Dummy1 result1 10 102
10 test3 t1 Dummy2 result2 20 106
11 test3 t1 Dummy3 result3 30 107
12 test3 t1 Dummy5 result4 50 110.2
13 test4 t1 Dummy2 result2 20 120
14 test5 t1 Dummy6 result1 100 88

Output should look like below:

col4 col5 col6 test1 test2 test3 test4 test5
Dummy1 result1 10 100.2 101 102
Dummy6 result1 100 88
Dummy2 result2 20 101.2 103 106 120
Dummy3 result3 30 102.3 104 107
Dummy4 result4 40 101.4 105
Dummy5 result4 50

Any guidance is much appreciated.

buggsbunny4
  • 115
  • 2
  • 12
  • You can look at [How to pivot a dataframe in Pandas?](https://stackoverflow.com/questions/28337117/how-to-pivot-a-dataframe-in-pandas) to transform dataframe into pivot table, also what you are trying to do is not possible using SQLite, you should first read the data from the SQLite and then you can use pandas pivot to get the desired behavior. – ThePyGuy Jun 04 '21 at 00:41
  • Just to highlight some key points: you can't do this in SQL. You will need to read the data in and process it in code. `Pandas` is a good choice. – Tim Roberts Jun 04 '21 at 00:45

0 Answers0