0

Not sure i am explaining it well in the title, I am trying to create a few views, but i am wondering if what i am wanting to accomplish is even possible.

I have a table:

|Column1|Column2|Column3|  Column4  |
|  1    |1234   | Foo   | 19/11/2021| 
|  2    |1234   | Foo   | 19/11/2021| 
|  3    |1234   | Foo   | 19/11/2021| 
|  1    |1234   | Bar   | 19/11/2021| 
|  2    |1234   | Bar   | 19/11/2021| 
|  3    |1234   | Bar   | 19/11/2021| 
|  1    |1234   | Baz   | 19/11/2021| 
|  2    |1234   | Baz   | 19/11/2021| 
|  3    |1234   | Baz   | 19/11/2021|

The Unique of the table on this is Column1, Column2, Column3.

When querying this data in a view, because of the type of join i am getting, i am getting Column1, and Column 2 showing multiple times because of the multiple entries in Column 3. This is what i expected.

But is there any way or query in sql that can do something like: for-each column 3, assigning it to column4, column5, column5.

For example:

|Column1|Column2|Column3|  Column4  |  Column3a |  Column3b |  Column3c |
|  1    |1234   | Foo   | 19/11/2021|   Foo     |   Bar     |   Baz     | 
|  2    |1234   | Foo   | 19/11/2021|   Foo     |   Bar     |   Baz     | 
|  3    |1234   | Foo   | 19/11/2021|   Foo     |   Bar     |   Baz     |

The number of unique in Column3 can be be any, so it would have to be programmable like a foreach column+1 This is working with existing data, so its not possible to reformat how its already present, i am working with a poorly designed database and trying to create views to make it more readable and workable for a API Endpoint

Dave Hamilton
  • 675
  • 1
  • 12
  • 23

1 Answers1

0

If I understand your problem correctly, the answer is no. There is no way in the mysql command-line to use such a logic. However you could write a tool to import the table, process column3 and move it to a new table. If you are only interested in the view, you can also just import the table and display it. With Python it is very easy to do this.

An entry point: https://www.w3schools.com/python/python_mysql_getstarted.asp

MiC
  • 1
  • Yes i was considering this, but the table is being populated in real-time from another data source. So i would have to have a program running every minute to re-create the table. I was hoping there was some functions in SQL that could apply this in a view using a sort of foreach unique logic, i didn't think this was possible but thanks for the clarification. – Dave Hamilton Nov 19 '21 at 14:12