0

I've been racking my brains for a while and I'm sure there is a simple solution to it, but for the life of me it's not obvious.

I want to query a database in MySQL Workbench to return a set of serial numbers for a given part number, which is of a fairly basic form:

Select serial_num as sn12345 
from process 
where part_number = 12345

Thus my output is

sn12345 
---------------
0000001 
0000002 
etc

Now I have a number of part numbers I want to get the serial numbers of so that my output is like

sn12345 | sn12346 | sn12347 |  
------------------------------------------
0000001  | 0000005 | 0000008 |
0000002  | 0000006 | 0000009 |

Assume that there are more columns than just these. However, I do not want to UNION the query as I want output in individual columns. Also, there may be different numbers of serial number entries for each part number, i.e 100 for one, but 1000 for a second, and 5 for a third, etc, so I'll probably have a lot of NULL entries.

Thanks in advance!

Krishna Rani Sahoo
  • 1,539
  • 1
  • 14
  • 25
Ben K
  • 1
  • What are sn12346 and sn12347, are these the columns? – Rishi Vedpathak Nov 17 '14 at 06:47
  • do you have a primary key for your 'process' table? what entities does the 'process' table have? – Kelvin Barsana Nov 17 '14 at 06:51
  • you should use MYSQL Pivot table. Example. http://stackoverflow.com/questions/7674786/mysql-pivot-table – Krishna Rani Sahoo Nov 17 '14 at 06:53
  • @RishiVedpathak Yes, those are column titles. All the serial numbers are listed in a "serial_num" column while the "product number" is listed in a separate column. I've used select ... as [name] to label the columns. – Ben K Nov 18 '14 at 00:05
  • @KrishnaRaniSahoo Thanks for the link. I've had a read through it after a fresh cup of coffee and after some thought it looks to be easier to batch run the different queries and copy paste over to Excel, which is where I end up having to process the data anyway. Many thanks – Ben K Nov 18 '14 at 00:26

0 Answers0