0

I have a SQL table_1 with the following data:

enter image description here

There are multiple entries with and the same MSISDN gets repeated, but with different servicCodes in each line.

I would like to copy the info into a new table_2, but with only one entry per MSISDN and some extra columns with the services assigned to it. Can this be done with a single SQL query, or is it be best to use PHP to do a distinct query and loop through the results?

Thanks.

Rodia
  • 1,407
  • 8
  • 22
  • 29
Louis Meyer
  • 35
  • 5
  • To clearify, the result must contain for each MSISDN the MSISDN, Service1 = GP26, Service 2 = MMS, Service 3 = A131 and so on untill the next MSISDN is reached. Then repear the process for the next entry. This could be simular to the pivot table layout of an excel work sheet. – Louis Meyer Feb 21 '17 at 16:12

3 Answers3

1

With SQL you can create a second table with a column with the servicCodes concatenated. You can use the GROUP BY to aggregate around the MSISDN column.

SELECT MSISDN, GROUP_CONCAT(servicCodes) as servicCodes_concatenated
FROM table
GROUP BY MSISDN

If you want to create multiple columns to have the values of servicCodes it's easier to use PHP.

Fredster
  • 776
  • 1
  • 6
  • 16
  • 1
    Super, thanks. It works fine so all I need to do to find out if a service code exist on a specified MSISDN is to run the LIKE query. Appreciate the quick responce. – Louis Meyer Feb 21 '17 at 16:29
0

Use INSERT ... SELECT syntax so you can filter and modify rows in single query. https://dev.mysql.com/doc/refman/5.7/en/insert-select.html

ponury-kostek
  • 7,824
  • 4
  • 23
  • 31
0

I'd guess what you're looking for is called a Pivot Table.

See mysql pivot table

Everything is most probably said there, so just one comment on them:

As you get dynamic column names per definition it's hard(er) to deal with those columns lateron. Thus you most probably need some more sophisticated procedures to make further/other use of that data than just reporting it.

Community
  • 1
  • 1
RuDevel
  • 694
  • 3
  • 14
  • @LouisMeyer: As I see you mentioned them in a later comment. Must have been while I was writing my answer. So I am sorry if I told you what you already knew. To avoid that I'd recommend to note such an important keyword within the title or the question. – RuDevel Feb 21 '17 at 16:53