Starting with the output from the query (test data for demonstration purposes):
category_name total grated mashed boiled
-----------------------------------------------------------
Artichoke 107 67 9 31
Pepper 65 38 2 25
Carrot 46 32 NULL 14
Lettuce 24 24 NULL NULL
Spinach 16 8 1 7
Zuchini 4 4 NULL NULL
Paste this data excluding the heading line into vim (or pipe it to an output file and then edit the file). Firstly lets get rid of those ugly null values:
:%s/NULL/0/g
This searches for the character sequence "NULL" and replaces it with 0 globally. The data should now look like this
Artichoke 107 67 9 31
Pepper 65 38 2 25
Carrot 46 32 0 14
Lettuce 24 24 0 0
Spinach 16 8 1 7
Zuchini 4 4 0 0
Next lets format the lines and add separators:
:%s/^ /|-^M| /g
This one is a bit trickier, the ^M is actually a control character indicating a new line and is created by pressing Ctrl-V and the Enter. It searches for lines starting with white space (^ ) and inserts |- and a new line before prefixing the line with a |:
|-
| Artichoke 107 67 9 31
|-
| Pepper 65 38 2 25
|-
| Carrot 46 32 0 14
|-
| Lettuce 24 24 0 0
|-
| Spinach 16 8 1 7
|-
| Zuchini 4 4 0 0
Finally we want to split each number onto a separate line. This is where the power of regular expressions really shines:
:%s/ \+\(\d\+\)/^M| \1/g
The regex is actually quite simple, it says find all instances with one or more spaces ( \+) followed by one or more digits (\d\+) and save the digits for substitution (by putting them in brackets \( and \)). Then replace the pattern with a newline, a pipe and the saved digits (\1). And viola, columns to rows:
|-
| Artichoke
| 107
| 67
| 9
| 31
|-
| Pepper
| 65
| 38
| 2
| 25
|-
| Carrot
| 46
| 32
| 0
| 14
|-
| Lettuce
| 24
| 24
| 0
| 0
|-
| Spinach
| 16
| 8
| 1
| 7
|-
| Zuchini
| 4
| 4
| 0
| 0
Pretty cool.
