Here’s one for you… imagine you have a properly normalised schema, but you need to output it to CSV for a spreadsheet. So, you have multiple attributes for each item. The number of items is larger than the max # of joins (61) that you can do in MySQL. How to do this in a single query?
From normalised schema to spreadsheet
6 thoughts on “From normalised schema to spreadsheet”
Comments are closed.
I would not. I would take Kettle (http://kettle.pentaho.org/) and use the denormalization step to do it for me.
If you really want to, you can use SELECT’s on GROUP_CONCAT and put that in the SELECT of your main query, but why annoy yourself? Life’s too short to bother with SQL for this kind of thing anyway…
Roland Bouman
multiple attributes for each item? multiple rows in the csv
alternatively, use GROUP_CONCAT to make one row per item in the csv
where is the problem???
;o)
rudy
http://r937.com/
The pure MySQL way:
Use an IF() statement as a column for every attribute you wish to use. This is tedious to do, so use Peter Brawley’s automatic pivot table stored procedure.
The easy way:
Use MS Excel to do one of the few things it’s actually good for: Making Pivot Tables.
You can use one of the core util’s for linux: join ; it’s a great tool for joining large text based files and much faster than sql.
http://www-128.ibm.com/developerworks/linux/library/l-textutils.html#9
If some questions have no answer, you’d still want a col… so you need to deal with a type of NULL situation.
[…] of the comments to last week’s scribble on joins referred to the POSIX join command. What does this mean? There is a commandline tool called […]