0

I have following tables in my application:

Table-1
--------------------------------------
|   id    |    column_2(array_type)   |
--------------------------------------
|    1    |    [20,21,22]             |
--------------------------------------

Table-2
---------------------------------------
|    id   |    column_2(array_type)    |
---------------------------------------
|    3    |    [31,32]                 |
---------------------------------------

Now I want to create a view as follows:

-----------------------------------------------------------------
|   id    |    Table_1_id    |    Table_2_id    |    column_2    |
-----------------------------------------------------------------
|    1    |        1         |       nil        |      20        |
-----------------------------------------------------------------
|    2    |        1         |       nil        |      21        |
-----------------------------------------------------------------
|    3    |        1         |       nil        |      22        |
-----------------------------------------------------------------
|    4    |       nil        |       3          |      31        |
-----------------------------------------------------------------
|    5    |       nil        |       3          |      32        |
-----------------------------------------------------------------

The view should have single row entry for every item in column_2'.

Am unable to split the column_2's array content to individual row and assign corresponding table's id to it.

Any help or reference would be very helpful. I do not have much experience with SQL.

Couldn't find much on this in google.

rAzOr
  • 300
  • 6
  • 19
  • Tag your question with the database you are using. – Gordon Linoff Jun 21 '17 at 14:28
  • You should try to do something by yourself before asking. – Mike Adamenko Jun 21 '17 at 14:31
  • @MikeAdamenko: I did try. But wasn't much successful. Couldn't find much references and am kinda stuck with no way to proceed/try. – rAzOr Jun 21 '17 at 14:35
  • What does your attempted query look like? – Marek Vitek Jun 21 '17 at 14:35
  • @MarekVitek: I did try that. But couldn't find how to split the array to individual row and assign the associated table's id. – rAzOr Jun 21 '17 at 14:36
  • I see. Your problem has nothing to do with creating view but with writing select . You are looking for [PIVOT](https://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx) – Marek Vitek Jun 21 '17 at 14:39
  • Oh forget PIVOT, it will not help you. You want to split data in one column into multiple rows. What a crazy person designed your DB. – Marek Vitek Jun 21 '17 at 14:47
  • Your question has already been answered on SO in [Splitting delimited values in a SQL column into multiple rows](https://stackoverflow.com/questions/11018076/splitting-delimited-values-in-a-sql-column-into-multiple-rows) Apply it on one table and then join results together. – Marek Vitek Jun 21 '17 at 14:51
  • @MarekVitek: Yes. Need to split the row to multiple rows. Is it possible/right to do this? – rAzOr Jun 21 '17 at 14:53
  • Yes it is possible https://www.periscopedata.com/blog/splitting-comma-separated-values-in-mysql.html – Marek Vitek Jun 21 '17 at 16:12

0 Answers0