0

I need to create a new table, based on two existing. http://sqlfiddle.com/#!9/74e49 It must contain number of instances of every particular value from table1 in table2. Like:

  • Name - koatuu1 - koatuu2 - koatuu3 - koatuu_N ... koatuu_350
  • Johnson - 5 - 12 - 76 - 9 - 321 - 4

It must be something like this for every lastname:

select table1.koatuu, count(table2.*) 
from table2 
where lastname = "Johnson"
inner join table1 on table1.postcode = table2.postcode

I need a red circled table Can I do it within MySQL query, without writing a script in Php?

Vyacheslav Butenko
  • 15
  • 2
  • Provide us table structures, example data (no images) place it on http://sqlfiddle.com/ or https://www.db-fiddle.com/ like the help guide is telling https://stackoverflow.com/help/how-to-ask ("How to create a Minimal, Complete, and Verifiable example.") Also provide us with the expected output. – Raymond Nijland Jul 26 '18 at 12:12
  • I'm doing it at the fist time, sorry. Is this it? http://sqlfiddle.com/#!9/74e49 – Vyacheslav Butenko Jul 26 '18 at 12:39
  • and what is your expected output for that sample fiddle? Edit the question with sample data and expected data – Lixas Jul 26 '18 at 12:47
  • I assume now looking into the sqlfiddle and the image you don't want to create a new table but a result based on the tables.. Besides this question is a duplicated search stackoverflow with "MySQL pivot" or "MySQL convert records into columns" or if you need it to be dynamic "MySQL dynamic pivot" – Raymond Nijland Jul 26 '18 at 12:49

2 Answers2

0

The red table has 2 columns (lastname and koatuu). To get all the koatuu values for each lastname use this query

SELECT lastname,GROUP_CONCAT(koatuu) FROM lastname_koatuu GROUP BY lastname
IVO GELOV
  • 13,496
  • 1
  • 17
  • 26
0

So, basically you want to transpose rows to columns

MySQL doesn't support ANSI PIVOT/UNPIVOT syntax, so that leave you to use MySQL's Prepared Statement (dynamic SQL) syntax.

You may use CASE for each row, like in SQL Transpose

Lixas
  • 6,938
  • 2
  • 25
  • 42