# Pivot non-numeric data? **URL:** \ **Category:** Kinetic ERP\ **Tags:** baq\ **Created:** [May 23, 2023, 4:03pm UTC](https://www.epiusers.help/t/pivot-non-numeric-data/103221 "2023-05-23T16:03:29Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![JasonMcD](https://www.epiusers.help/user_avatar/www.epiusers.help/jasonmcd/32/22326_2.png) [@JasonMcD](https://www.epiusers.help/u/JasonMcD)\ **Post date:** [May 23, 2023, 4:03pm UTC](https://www.epiusers.help/t/pivot-non-numeric-data/103221/1 "2023-05-23T16:03:29Z")
This is a BAQ question, but I will illustrate in Excel. I have this data - job numbers and some serial-tracked part numbers that were issued to those jobs. I categorized them into 12 groups, like: - Engine - Transmission - Front axle - Rear axle I want a result with those categories as column headers and a **single** job number per row, with **the part number** of the component in each category column. Like so: ![image](https://www.epiusers.help/uploads/default/original/3X/5/7/5763dc266ec344ce62c79280d6a1100fd77a6588.png) In my mind, I have “pivot-ed” the data, but a “pivot” - in SQL _and_ Excel - is an aggregation of numeric data, or a count of non-numeric data, but that’s it - the result is always numeric, not some field from a row. Right? Or could this be done in a BAQ? The only way I know would be death by 12 subqueries. In Excel, I hacked it like this. I numbered every row, then pivoted the “sum” of those row numbers, but it’s 1:1, so I end up with an index that I can VLOOKUP off of. Anyone have a better way (in Epicor)? ![image](https://www.epiusers.help/uploads/default/original/3X/c/6/c62afea53152736619aa12f5922bb4e6f25a2971.png) **Sheet1:** ![image](https://www.epiusers.help/uploads/default/original/3X/0/7/079e8d6bf42c6bb7fb3fc5c01c1f5b9acd8dd9d7.png) ---
**Author:** ![NateS](https://www.epiusers.help/user_avatar/www.epiusers.help/nates/32/18519_2.png) [@NateS](https://www.epiusers.help/u/NateS)\ **Post date:** [May 23, 2023, 5:42pm UTC](https://www.epiusers.help/t/pivot-non-numeric-data/103221/2 "2023-05-23T17:42:17Z")
> [@JasonMcD](#): > > The only way I know would be death by 12 subqueries. This is the way. Yes, it sucks to make a separate subquery for each one, but if you know you need that many from the beginning you can make it a little cleaner by naming each subquery for the category it will return results for. That makes the visual joins a little easier to read. Then just join it all up on the top level by job number. It looks like you are not trying to return serial numbers just the part numbers, so these subqueries should be pretty simple. Post up your BAQ when you get close and we can help fine tune it. If there really is a way to do this without a subquery for each category, I would love to learn it too! ---
**Author:** ![jkane](https://www.epiusers.help/user_avatar/www.epiusers.help/jkane/32/57624_2.png) [@jkane](https://www.epiusers.help/u/jkane)\ **Post date:** [May 23, 2023, 5:45pm UTC](https://www.epiusers.help/t/pivot-non-numeric-data/103221/3 "2023-05-23T17:45:14Z")
Are the parts identifiable in Epicor? Like the part class is engine or something? ---
**Author:** ![JasonMcD](https://www.epiusers.help/user_avatar/www.epiusers.help/jasonmcd/32/22326_2.png) [@JasonMcD](https://www.epiusers.help/u/JasonMcD)\ **Post date:** [May 23, 2023, 5:46pm UTC](https://www.epiusers.help/t/pivot-non-numeric-data/103221/4 "2023-05-23T17:46:13Z")
> [@NateS](#): > > This is the way Resisting the urge to put a GIF here… I already started down the 12-subquery path. I’m up to 6! All in all, I think I have the same logic you described. ![image](https://www.epiusers.help/uploads/default/original/3X/d/7/d756e59693292c3367452aeb6cc284b632c0833a.png) ---
**Author:** ![JasonMcD](https://www.epiusers.help/user_avatar/www.epiusers.help/jasonmcd/32/22326_2.png) [@JasonMcD](https://www.epiusers.help/u/JasonMcD)\ **Post date:** [May 23, 2023, 5:47pm UTC](https://www.epiusers.help/t/pivot-non-numeric-data/103221/5 "2023-05-23T17:47:52Z")
> [@jkane](#): > > part class is engine Not exactly, but I have _enough_ to go on. ![image](https://www.epiusers.help/uploads/default/original/3X/3/b/3b3a04c26336063c54dcb719e12522d9ee651599.png) Subquery is already filtered to `TrackSerialNum = 1` ---
**Author:** ![jkane](https://www.epiusers.help/user_avatar/www.epiusers.help/jkane/32/57624_2.png) [@jkane](https://www.epiusers.help/u/jkane)\ **Post date:** [May 23, 2023, 5:52pm UTC](https://www.epiusers.help/t/pivot-non-numeric-data/103221/6 "2023-05-23T17:52:45Z")
You could just do a calculated field for each one. If there is only 1 or none of each on a job. ---
**Author:** ![JasonMcD](https://www.epiusers.help/user_avatar/www.epiusers.help/jasonmcd/32/22326_2.png) [@JasonMcD](https://www.epiusers.help/u/JasonMcD)\ **Post date:** [May 23, 2023, 5:55pm UTC](https://www.epiusers.help/t/pivot-non-numeric-data/103221/7 "2023-05-23T17:55:07Z")
> [@jkane](#): > > calculated field @jkane Right, but that doesn’t get me off the hook for bringing in 12 subqueries, right? ---
**Author:** ![jkane](https://www.epiusers.help/user_avatar/www.epiusers.help/jkane/32/57624_2.png) [@jkane](https://www.epiusers.help/u/jkane)\ **Post date:** [May 23, 2023, 5:56pm UTC](https://www.epiusers.help/t/pivot-non-numeric-data/103221/8 "2023-05-23T17:56:14Z")
Sure, why would it not? If every job has one or none of each category, just do a case statement looking for the description and then return the part number. ---
**Author:** ![NateS](https://www.epiusers.help/user_avatar/www.epiusers.help/nates/32/18519_2.png) [@NateS](https://www.epiusers.help/u/NateS)\ **Post date:** [May 23, 2023, 5:58pm UTC](https://www.epiusers.help/t/pivot-non-numeric-data/103221/9 "2023-05-23T17:58:55Z")
Thats a good point @jkane! If you know you only have one part per job for each class then this should work. ---
**Author:** ![jkane](https://www.epiusers.help/user_avatar/www.epiusers.help/jkane/32/57624_2.png) [@jkane](https://www.epiusers.help/u/jkane)\ **Post date:** [May 23, 2023, 5:59pm UTC](https://www.epiusers.help/t/pivot-non-numeric-data/103221/10 "2023-05-23T17:59:51Z")
I think you would need a MAX too. ```auto MAX( Case When Then Else 0 End) ``` ---
**Author:** ![JasonMcD](https://www.epiusers.help/user_avatar/www.epiusers.help/jasonmcd/32/22326_2.png) [@JasonMcD](https://www.epiusers.help/u/JasonMcD)\ **Post date:** [May 23, 2023, 6:01pm UTC](https://www.epiusers.help/t/pivot-non-numeric-data/103221/11 "2023-05-23T18:01:40Z")
[**Edit** - let’s say JobHead instead] Ok, so let’s pretend the center table is… the JobHead table. And the other is essentially JobMtl (joined to Part, for the TrackSerialNum field). How would I join “JobHead” to the JobMtl subquery? ---
**Author:** ![jkane](https://www.epiusers.help/user_avatar/www.epiusers.help/jkane/32/57624_2.png) [@jkane](https://www.epiusers.help/u/jkane)\ **Post date:** [May 23, 2023, 6:03pm UTC](https://www.epiusers.help/t/pivot-non-numeric-data/103221/12 "2023-05-23T18:03:41Z")
Just load the three tables. JobHead as 1, JobMtl as 2, and Part as 3. Join them and you are good to go. 1 Query. ---
**Author:** ![JasonMcD](https://www.epiusers.help/user_avatar/www.epiusers.help/jasonmcd/32/22326_2.png) [@JasonMcD](https://www.epiusers.help/u/JasonMcD)\ **Post date:** [May 23, 2023, 6:04pm UTC](https://www.epiusers.help/t/pivot-non-numeric-data/103221/13 "2023-05-23T18:04:06Z")
But I’d get many rows for one job, right? ---
**Author:** ![jkane](https://www.epiusers.help/user_avatar/www.epiusers.help/jkane/32/57624_2.png) [@jkane](https://www.epiusers.help/u/jkane)\ **Post date:** [May 23, 2023, 6:05pm UTC](https://www.epiusers.help/t/pivot-non-numeric-data/103221/14 "2023-05-23T18:05:33Z")
I don’t know. I am just thinking of the Job Number (which will be grouped) and the columns for the part category (which will have the MAX). Is there other data in the query? ---
**Author:** ![jkane](https://www.epiusers.help/user_avatar/www.epiusers.help/jkane/32/57624_2.png) [@jkane](https://www.epiusers.help/u/jkane)\ **Post date:** [May 23, 2023, 6:07pm UTC](https://www.epiusers.help/t/pivot-non-numeric-data/103221/15 "2023-05-23T18:07:05Z")
Just try it with JobNum and Engine only. See what happens. If it works like you want, we can continue to extrapolate the rest of the query. ---
**Author:** ![JasonMcD](https://www.epiusers.help/user_avatar/www.epiusers.help/jasonmcd/32/22326_2.png) [@JasonMcD](https://www.epiusers.help/u/JasonMcD)\ **Post date:** [May 23, 2023, 6:09pm UTC](https://www.epiusers.help/t/pivot-non-numeric-data/103221/16 "2023-05-23T18:09:19Z")
> [@jkane](#): > > the Job Number (which will be grouped) and the columns for the part category (which will have the MAX) Ahh, interesting! I can see this. That just might work. ---
**Author:** ![JasonMcD](https://www.epiusers.help/user_avatar/www.epiusers.help/jasonmcd/32/22326_2.png) [@JasonMcD](https://www.epiusers.help/u/JasonMcD)\ **Post date:** [May 23, 2023, 6:35pm UTC](https://www.epiusers.help/t/pivot-non-numeric-data/103221/17 "2023-05-23T18:35:01Z")
@jkane Brilliant. Runs in half the time and was much easier to produce. ![image](https://www.epiusers.help/uploads/default/original/3X/f/f/ffcf618b05975d82b770b3ae4b60bdf62cb40253.png) ---
**Author:** ![jkane](https://www.epiusers.help/user_avatar/www.epiusers.help/jkane/32/57624_2.png) [@jkane](https://www.epiusers.help/u/jkane)\ **Post date:** [May 23, 2023, 6:36pm UTC](https://www.epiusers.help/t/pivot-non-numeric-data/103221/18 "2023-05-23T18:36:55Z")
Excellent! Glad I could help. ---
**Author:** ![klincecum](https://www.epiusers.help/user_avatar/www.epiusers.help/klincecum/32/100137_2.png) [@klincecum](https://www.epiusers.help/u/klincecum)\ **Post date:** [May 23, 2023, 7:50pm UTC](https://www.epiusers.help/t/pivot-non-numeric-data/103221/19 "2023-05-23T19:50:21Z")
> [@JasonMcD](#): > > Resisting the urge to put a GIF here… > [@BAQ Art](https://www.epiusers.help/t/baq-art/101812): > > Who’s got some pretty BAQ art? smiley #Diamond This is a real gem of a BAQ, am I right?? This is @josecgomez’s favorite BAQ now. He loves it. True story. ---
**Author:** ![JasonMcD](https://www.epiusers.help/user_avatar/www.epiusers.help/jasonmcd/32/22326_2.png) [@JasonMcD](https://www.epiusers.help/u/JasonMcD)\ **Post date:** [May 23, 2023, 7:58pm UTC](https://www.epiusers.help/t/pivot-non-numeric-data/103221/20 "2023-05-23T19:58:11Z")
Well I don’t know why you linked to that but I did somehow miss that whole (55-post) thread, so that was, um, worth it? Anyway, you liked the sunburst? (Which is now abandoned.) Or is it a flowerbox? ![image](https://www.epiusers.help/uploads/default/original/3X/2/8/284ee24189306d283d0ddfae1db476e20694b45c.png)