A friend of mine asked for help to do a complicated data pivot on a few thousand rows of data. \u00a0We discussed it and agreed that we probably needed to walk each row\u00a0and do an algorithmic summation on the fly.<\/p>\n
My first instinct was to fire up Windows and dust off the old VBA cobwebs in my mind. \u00a0Fortunately that didn’t last long, and I reached for a far more comfortable friend, SQL. \u00a0The final SQL wasn’t terribly simple, but wasn’t terribly complicated either. \u00a0And it was a total linux solution, always the more pleasant solution. \u00a0And totally gui, too, for all you folks who\u00a0get hives when you have to bang at a terminal. \u00a0\ud83d\ude42<\/p>\n
BAM, easy, powerful, free, cheap, fast, win, win, win!<\/p>\n
I ended up using this sql, the key is sqlite’s\u00a0group_concat(), which just jams a whole query into one field, ha, boom.<\/p>\n
SELECT a.email<\/code>\r\n , group_concat(d.Dates) AS Dates<\/code>\r\n , group_concat(d.Values) ASValues<\/code>\r\nFROM (select * from TestData\u00a0ORDER BY email,Dates) AS a<\/code>\r\ninner JOIN TestData AS d ON d.email = a.email<\/code>\r\nGROUP BY a.email;<\/code><\/pre>\nThen I copied\u00a0the tabular result of the query, and pasted into\u00a0the LibreOffice\u00a0sheet, which asked me how to import. \u00a0Delimited by tab and comma, and then always recheck “select text delimiter” and use a comma. \u00a0And the cells fill right up.<\/p>\n
All is full of light.<\/p>\n","protected":false},"excerpt":{"rendered":"
A friend of mine asked for help to do a complicated data pivot on a few thousand rows of data. \u00a0We discussed it and agreed that we probably needed to walk each row\u00a0and do an algorithmic summation on the fly. My first instinct was to fire up Windows and dust off the old VBA cobwebs […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_feature_clip_id":0,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2},"jetpack_post_was_ever_published":false},"categories":[19,10],"tags":[263,264,262,261],"class_list":["post-2068","post","type-post","status-publish","format-standard","hentry","category-opensource","category-tricks-tips-tools","tag-excel","tag-group_concat","tag-sql","tag-sqlite"],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t