# Pivot BAQ

**URL:** <https://www.epiusers.help/t/pivot-baq/111402>\
**Category:** Kinetic ERP\
**Tags:** baq\
**Created:** [February 5, 2024, 3:03pm UTC](https://www.epiusers.help/t/pivot-baq/111402 "2024-02-05T15:03:21Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![jseidl](https://www.epiusers.help/letter_avatar_proxy/v4/letter/j/85f322/32.png) [@jseidl](https://www.epiusers.help/u/jseidl)\
**Post date:** [February 5, 2024, 3:03pm UTC](https://www.epiusers.help/t/pivot-baq/111402/1 "2024-02-05T15:03:21Z")

</div>

Hi All,

I’m working with a set of data to show our on hand quantity by site, using the PartWhse table. I’m attempting to pivot that data to show our sites as columns and the on hand values in rows. When I pivot it, I am getting three rows for each part number displaying an on hand value for a separate site on each line. IS there any way to combine these into a single row per line? What am I missing here?

Example of the data I’m seeing below.

![image](https://www.epiusers.help/uploads/default/original/3X/8/f/8f0f5e8739e263f176afbe2736875d02b9791eb1.png)

---

<div class="post-metadata">

**Author:** ![kve](https://www.epiusers.help/user_avatar/www.epiusers.help/kve/32/76150_2.png) [@kve](https://www.epiusers.help/u/kve)\
**Post date:** [February 5, 2024, 4:00pm UTC](https://www.epiusers.help/t/pivot-baq/111402/2 "2024-02-05T16:00:27Z")

</div>

I’d use a calculated field for each column. Something like

```auto
sum( CASE WHEN PartBin.BinNum = 'BIN#1' THEN PartBin.OnHandQuantity ELSE 0 END )

```

You could do one calculated field (replacing the BinNum) for each bin.

---

<div class="post-metadata">

**Author:** ![HLalumiere](https://www.epiusers.help/letter_avatar_proxy/v4/letter/h/bc8723/32.png) [@HLalumiere](https://www.epiusers.help/u/HLalumiere)\
**Post date:** [February 5, 2024, 5:12pm UTC](https://www.epiusers.help/t/pivot-baq/111402/3 "2024-02-05T17:12:19Z")

</div>

Group by the key and sum the other columns.

---

<div class="post-metadata">

**Author:** ![Banderson](https://www.epiusers.help/user_avatar/www.epiusers.help/banderson/32/50875_2.png) [@Banderson](https://www.epiusers.help/u/Banderson)\
**Post date:** [February 5, 2024, 5:30pm UTC](https://www.epiusers.help/t/pivot-baq/111402/4 "2024-02-05T17:30:20Z")

</div>

The pivot will work like a group by, and columns in the feeding subquery need to all be grouped. So if you have extra columns in the subquery, that will cause the rows to not correctly condense. (even if they aren’t displayed)

Here’s an example.

This is my subquery that feeds the pivot. I am pivoting on part number with warehouse transaction qty. I don’t care about BinNum in my final display, but I have it here.

 ![image](https://www.epiusers.help/uploads/default/original/3X/8/d/8d4ed37a792d6b3a10f46f24b2ecbeff6df18010.png)

These are the results I get.  
 ![image](https://www.epiusers.help/uploads/default/original/3X/8/b/8b894af43cb47834e1aa11653c1ba6023f352f7e.png)

But if I remove Bin Number

 ![image](https://www.epiusers.help/uploads/default/original/3X/1/f/1f69b3aaed8cb0349d94786d0e37a27d208feb75.png)

I get one row as expected.  
 ![image](https://www.epiusers.help/uploads/default/original/3X/4/8/481bdb0a4fbc802a88383997edf327c41c7c22f5.png)